Make Excel's ERF function work correctly for negative arguments (.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Excel 2003 seems to have broken the ERF function (available in the Analysis
ToolPak). Correct behavior is that ERF(-x) = -ERF(x), but instead we get
#NUM! for negative arguments - a serious error. My recollection is that ERF
worked correctly in previous versions, but I'm not sure of that.

ERFC suffers from the same problem. Strangely, NORMSDIST does not.

ERF and ERFC are mathematically defined for both positive and negative
argument values. The fix is easy. This problem should be corrected.
 
It's worse than I thought. Not only are the results #NUM! for negative
argument, they are also #NUM! for positive arguments greater than about
27.3345, where we should get ERF = 1 and ERFC = 0 instead of failure.

Even where they work, the results are not very accurate. ERFC has relative
error 10^-6 around x = 0.75, and then suffers from roundoff for x > 4, losing
all accuracy around x = 6. Apparently, Excel uses ERFC = 1 - ERF, an awful
choice.

You can use the mathematical identities ERF(x) = 2*NORMSDIST(x*SQRT(2))-1
and ERFC(x) = 2*NORMSDIST(-x*SQRT(2)) to get numeric answers for negative x,
but errors are still large. For example, ERFC has relative error around
6x10^-7 at x = 3.5 when gotten from NORMSDIST. But over most of the range of
x, the NORMSDIST methods are the best of a bad lot.

The NORMSDIST forms are more accurate than ERF(x) =
GAMMADIST(x^2,0.5,1,True) and ERFC(x) = CHIDIST(2*x^2,1) - and these only
work for positive x, anyway.
 
Your recollection is faulty. ERF and ERFC have not changed since they were
introduced with the Analysis ToolPak. NORMSDIST was greatly enhanced in 2003
(previously it was slightly worse than erfc for the negative tail of the
normal distribution.

This observation/suggestion has nothing to do with an Excel crash or GPF.
Had it been posted in Worksheet Functions where it belonged, It would have
gotten much more exposure. I only stumbled on it by accident, while doing a
keyword search across Excel newsgroups.

Jerry
 
Quite true, Jerry. I was led astray by the fact that I used the web page, and
the web drop-down name for this group is "Excel Application Errors." It
seemed to be the only way to report errors. The first time I saw
"crashesgpfs" was in your post. I have now been able to reach actual humans
at MS (via my corporate support contract) and they have added error notes to
the Knowledge Base (893352, though they got the ERFC fix wrong). They are
aware of the negative-argument problem as well. They've promised to fix it
all in the next release, and have asked me to be a beta tester.
 
John Trenholme said:
... I have now been able to reach actual humans
at MS (via my corporate support contract) and they have added error notes to
the Knowledge Base (893352, though they got the ERFC fix wrong).
Click to expand...

The ERF note is also an overstatement. Subtracting one guarantees lost
precision near zero. For arguments 0 < x < 2^(-22), the existing ERF
function is more accurate than the proposed workaround. I submitted both
corrections. Hopefully with both of us pushing on it, it will get done right.
They are
aware of the negative-argument problem as well. They've promised to fix it
all in the next release, and have asked me to be a beta tester.
Click to expand...

Sounds good.

Jerry
 
Excel erf bug

Hello!

erf has also one another problem : accuracy is only 7 decimal on some domains.

Perhaps the following implementation may help:

Function Derf# (x#) 'double precision erf function
Const PI = 3.14159265358979
Const epsilon# = .000000000000001
a# = Exp(-x# * x#) / Sqr(PI)
If (x# <= 2) Then
a# = 2 * x# * a#
s# = a#
nu# = 1 / 2
Do
a# = x# * x# * a# / (nu# + 1)
s# = s# + a#
nu# = nu# + 1
Loop While (a# > epsilon#)
Derf# = s#
Else
m# = 8
z# = x#
Do
z# = x# + m# / z#
m# = m# - 1 / 2
Loop While (m# > 0#)
Derf# = 1 - a# / z#
End If
End Function


Happy programming
IceCactus
 
Last edited:
For extremely small x (such as 2^-900), the ATP ERF() has no figures correct.
Your implementation is uniformly better than the ATP ERF(). It could be
slightly improved by using WorksheetFunction.Pi(), which is almost 2 figures
more accurate than your constant.

Another approach is to use =cdf_gamma(x^2,0.5,1) for the highly accurate
implementation of the gamma distribution in
http://members.aol.com/iandjmsmith/Examples.xls
Your implementation is slightly more accurate for small values of x (and
obviously more accurate for x so small that x^2 is numerically zero), but you
go down to only 10 figure accuracy for small values of ABS(x-2), where the
cdf_gamma approach maintains near machine accuracy.

Jerry
 
Back
Top