ERF()
Bill Brehm
above ~ 27 give a bad result when any number should be legal? Is there an
inverse error function or any way to derive it or calculate it? Is there an
ERF() available in Visual Basic for Excel?
Thanks,
Bill
mows
Well, I think that it is reasonable to assume that Erf anything >= 6 is 1.
If we deal with Erfc ( =1 - Erf ) then Erfc(6) = 2*10^-17 and Erfc(27) is
about 5*10^-319. If you actually do want to calculate Erfc(>6) you could try
to use an approximation like
Public Function ErfComp(x)
x2 = x * x
SqPi = Sqr(Application.Pi())
ErfComp = (105 / 16 + x2 * (-15 / 8 + x2 * (3 / 4 + x2 * _
(-1 / 2 + x2)))) / x2 / x2 / x2 / x2
ErfComp = Exp(-x2) * ErfComp / x / SqPi
End Function
Excel cells will go down to about 1E-308, so ErfComp(26) will have a cell
value of 5.6632E-296 but ErfComp(27) will be 0
Probably what you actually want to stop #NUM is something like
=IF(A1>27,1,ERF(A1))
or
=IF(A1>27,0,ERFC(A1))
mows
ExcelXP / WinXP
Jerry W. Lewis
do from VBA). You could use the following relations (assumes x>=0)
ERFC(x) = 2*NORMSDIST(-x*SQRT(2)) = CHIDIST(2*x^2,1)
ERF(x) = 1-ERFC(x) = GAMMADIST(x^2,0.5,1,True)
so that
ERFCinv(p) = -NORMSINV(p/2)/SQRT(2) = SQRT(CHIINV(p,1)/2)
ERFinv(p) = SQRT(GAMMAINV(p,0.5,1)) = -NORMSINV((1-p)/2)/SQRT(2)
CHIDIST is more accurate for large x than any of ERF, ERFC, NORMSDIST,
or GAMMADIST.
No inverse functions in Excel are terribly accurate, although NORMSINV
in Excel XP is much improved.
Jerry
Bill Brehm
test into their ERF() implementation.
Btw, I'm using Excel 2000. Does the same problem occur in their latest
version?
Bill
"mows" <mo...@mopar.freeserve.co.uk> wrote in message
news:epIHkndgCHA.2636@tkmsftngp08...
mows
>It's ashame MS didn't just build that simple
>test into their ERF() implementation.
Yes it really does seem such a simple correction. The defence could be that
asking for ERF(27) is unlikely and that you may have made a mistake -- #NUM
alerts you to that potential error!! If you want to write you own ERF()
function here is a place to start
W. J. Cody, Rational Chebyshev approximations for the error function,
Math.Comp., pp. 631-638, 1969.
http://www.netlib.org/specfun/erf
It seems that Microsoft have been reluctant to 'repair' far greater woes
with Excel stats. For example your earlier question about generating the
normal distribution. The answer from Leo Heuser is of course spot on.
However ask for a large set on numbers ~10,000 and you may find that some of
the generated numbers can be many many SD's out of line and also many of the
generated numbers may be repeated. I just tried generating 30000 numbers
that way mean 0 SD 1. On sorting the output I immediately see the minimum
value
of -9.536743164 repeated 3 times. -1.486487236
repeated 8 times
>I'm using Excel 2000. Does the same problem occur in their latest
>version?
Yes, it does appear so. ExcelXP SP2 does the same =ERF(26) = 0 and =ERF(27)
= #NUM. The transition being around 27.334517
mows
WinXP / ExcelXP
Jerry W. Lewis
> Bill,
>
>
>>It's ashame MS didn't just build that simple
>>test into their ERF() implementation.
>>
>
> Yes it really does seem such a simple correction. The defence could be that
> asking for ERF(27) is unlikely and that you may have made a mistake -- #NUM
> alerts you to that potential error!! If you want to write you own ERF()
> function here is a place to start
>
> W. J. Cody, Rational Chebyshev approximations for the error function,
> Math.Comp., pp. 631-638, 1969.
>
> http://www.netlib.org/specfun/erf
or use 1-CHIDIST(2*x^2,1) as I suggested a few days ago. For x>1
CHIDIST(2*x^2,1) rapidly improves from 6 figure accuracy for ERFC(x) to
nearly 12 figure accuracy in the extreme tails.
mows
Quite agree; I am aware of your formulae, and I did see your earlier post.
Sometimes, I hope you agree, it's nice to have a bash a doing something from
scratch. I realise that generally a quick solution is needed hence
=IF(A1>27,1,ERF(A1)). It was just a thought.
mows