ERFC

[Gauss]

When I calculate ERFC(-2) EXCEL returns #NUM instead of 1.995322265. Anyone has a solution?

 

[Jim]

This is an excerpt from Help on the ERFC function:
"...
If x is nonnumeric, ERFC returns the #VALUE! error value.
If x is negative, ERFC returns the #NUM! error value. ."

Since your number is negative, it returns the #NUM! error.

--
Greeting from the Gulf Coast!
http://myweb.cableone.net/twodays

When I calculate ERFC(-2) EXCEL returns #NUM instead of 1.995322265.

Anyone has a solution?

 

[Harlan Grove]

When I calculate ERFC(-2) EXCEL returns #NUM instead of 1.995322265.
Anyone has a solution?

Microsoft didn't bother to support negative arguments, but the function is
symmetric at zero, so ERFC(-x) = -ERFC(x), so use

=IF(X>=0,ERFC(X),-ERFC(-X))

 

[Gauss]

-----Original Message-----


Microsoft didn't bother to support negative arguments, but the function is
symmetric at zero, so ERFC(-x) = -ERFC(x), so use

=IF(X>=0,ERFC(X),-ERFC(-X))


.

Thanks,
However, the inversion point is (0,1), so it should be:
=IF(X>=0,ERFC(X),2-ERFC(-X))

 

[Dana DeLouis]

I might be wrong, but if it is not symmetric, then see if this might
work....

=IF(A1<=0,1+ERF(ABS(A1)),ERFC(A1))

 

[Gauss]

I want to calculate
=ERFC(-(LN(A7)+$A$3^2*LN(10)^2)/($A$3*LN(10)*SQRT(2)))
which I should replace by
=IF((-(LN(A7)+$A$3^2*LN(10)^2)/($A$3*LN(10)*SQRT(2)))<0,1+ERF((-(LN(A7)+$A$3^2*LN(10)^2)/($A$3*LN(10)*SQRT(2)))),ERFC((-(LN(A7)+$A$3^2*LN(10)^2)/($A$3*LN(10)*SQRT(2)))))
or I can put in a new cell, say X9: (-(LN(A7)+$A$3^2*LN(10)^2)/($A$3*LN(10)*SQRT(2)))
and do
=IF(X9<=0,1+ERF(X9),ERFC(X9))
The first option leads to an unreadable long equation, for the second option I need an extra cell that ruins the layout of my worksheet.
Is there a more elegant solution?

Regards, Gauss

 

[Harlan Grove]

...
...

However, the inversion point is (0,1), so it should be:
=IF(X>=0,ERFC(X),2-ERFC(-X))

Oops. ERF is symmetric.

 

[Harlan Grove]

I want to calculate

=ERFC(-(LN(A7)+$A$3^2*LN(10)^2)/($A$3*LN(10)*SQRT(2)))

which I should replace by
[reformatted]

=IF((-(LN(A7)+$A$3^2*LN(10)^2)/($A$3*LN(10)*SQRT(2)))<0,
1+ERF((-(LN(A7)+$A$3^2*LN(10)^2)/($A$3*LN(10)*SQRT(2)))),
ERFC((-(LN(A7)+$A$3^2*LN(10)^2)/($A$3*LN(10)*SQRT(2)))))

or I can put in a new cell, say X9:
(-(LN(A7)+$A$3^2*LN(10)^2)/($A$3*LN(10)*SQRT(2)))
and do
=IF(X9<=0,1+ERF(X9),ERFC(X9))

The first option leads to an unreadable long equation, for the second option I
need an extra cell that ruins the layout of my worksheet. Is there a more
elegant solution?

Nibbling at the edges, look at your argument (removing unnecessary parentheses),

-(LN(A7)+$A$3^2*LN(10)^2)/($A$3*LN(10)*SQRT(2))<0

is equivalent to

(LN(A7)+$A$3^2*LN(10)^2)/($A$3*LN(10)*SQRT(2))>0

Now some algebraic reduction,

(LN(A7)+($A$3*LN(10))^2)/($A$3*LN(10)*SQRT(2))>0

(LN(A7)+($A$3*LN(10))^2)/($A$3*LN(10))>0

(LN(A7)+($A$3*LN(10))^2)/$A$3>0

Try

=1-SIGN((LN(A7)+($A$3*LN(10))^2)/$A$3)
*ERF(ABS((LN(A7)+($A$3*LN(10))^2)/($A$3*LN(10)*SQRT(2)))

which is still long, but (at least to me) not unreadably so. I doubt you could
shorten this much further without user-defined functions.