What is mathematical equation for NORMDIST (x,mean,standard_dev,TR

[Guest]

Would someone tell me the exact mathematical equation for NORMDIST
(x,mean,standard_dev,TRUE)?

I have found an equation in Excel Help which is for cumulative = FALSE and
written that the formula should integrate from negative infinity to x for
TRUE.

There are some equations for such integration in mathworld.wolfram.com with
error function (erf) but it does not give the same result as does the Excel
give.

For example, NORMDIST(0.3,4,1.5,TRUE) = 0.006819 (from Excel) and now I
wanted to know the equation which gave that value (0.006819). Any help in
this regard would be highly appreciated.

 

[Jerry W. Lewis]

NORMDIST(x,mean,standard_dev,TRUE) = NORMSDIST((x-mean)/standard_dev)

There is not a closed form for for NORMSDIST; it is the result of
numerical integration that is usually evaluated as a Taylor series or a
continued fraction.

Some exact and approximate formulas are given in books on statistical
computing, or books such as "Handbook of Mathematical Functions" by
Abramowitz and Stegun
http://jove.prohosting.com/~skripty/page_932.htm
or "An Atlas of Functions" by Spannier and Oldham". An implementation
in VBA is posted in
http://groups-beta.google.com/group/microsoft.public.excel.misc/msg/a538d6ddefaed7be?hl=en
Or see
http://members.aol.com/iandjmsmith/Examples.xls
for a more comprehensive library that is of excellent quality.

Some other identities that express NORMSDIST in terms of other functions
that have no closed form are
NormSDist(x) = ErfC(-x/Sqrt(2))/2 = (1-Erf(-x/Sqrt(2)))/2 for x<=0
NormSDist(x) = 1-ErfC(x/Sqrt(2))/2 = (1+Erf(x/Sqrt(2)))/2 for x>=0
NormSDist(x) = (1–GammaDist(x^2/2,1/2,1,TRUE))/2 for x<=0
NormSDist(x) = (1+GammaDist(x^2/2,1/2,1,TRUE))/2 for x>=0
NormSDist(x) = ChiDist(x^2,1)/2 for x<=0
NormSDist(x) = 1-ChiDist(x^2,1)/2 for x>=0

The ChiDist formula for x<<0 is far more accurate than NormSDist unless
you have Excel 2003.

Jerry

 

[Dana DeLouis]

Hi. Just to add. One problem you may have had with Excel's Erf function is
that Excel can not take the Erf of a negative number.

=ERF(0.3-4)
Returns The #Num! error.

But that web site was assuming you could do it normally, as in other math
programs...

Erf[0.3 - 4]
-0.9999998328489421

So, with your numbers...

(m=4, sd=1.5, x=.3)

=(1 + SIGN(x-m)* ERF(ABS(x-m)/(SQRT(2)*sd)))/2

returns: 0.006819...

 

[Guest]

Dear Jerry and Dana,

Thanks for your replies. But still I could not calculate NORMDIST without
using Excel. Would you please check the following equations which I have used
for ERF?
For z less than 1, ERF = 2/SQRT (pi) * e^(-z^2) * z (1+ (2z^2)/3 +
((2z^2)^2)/15 + …
For z greater than 1, ERF = 1- (e^(-z^2))/(SQRT(pi)) * (1/z - 1/(2z^3) +
3/(4z^5) -….)

Regards,

Rijan

Hi. Just to add. One problem you may have had with Excel's Erf function is
that Excel can not take the Erf of a negative number.

=ERF(0.3-4)
Returns The #Num! error.

But that web site was assuming you could do it normally, as in other math
programs...

Erf[0.3 - 4]
-0.9999998328489421

So, with your numbers...

(m=4, sd=1.5, x=.3)

=(1 + SIGN(x-m)* ERF(ABS(x-m)/(SQRT(2)*sd)))/2

returns: 0.006819...

--
Dana DeLouis
Win XP & Office 2003

Would someone tell me the exact mathematical equation for NORMDIST
(x,mean,standard_dev,TRUE)?

I have found an equation in Excel Help which is for cumulative = FALSE and
written that the formula should integrate from negative infinity to x for
TRUE.

There are some equations for such integration in mathworld.wolfram.com
with
error function (erf) but it does not give the same result as does the
Excel
give.

For example, NORMDIST(0.3,4,1.5,TRUE) = 0.006819 (from Excel) and now I
wanted to know the equation which gave that value (0.006819). Any help in
this regard would be highly appreciated.

 

[Jerry W. Lewis]

Both formulas are correct, but the range of application is a bit off.

The second formula is an asymptotic expansion. That means that if you
use enough terms, it will fail to converge for any finite z. You can
stop the sum when the next term is larger in magnitude than the previous
one, but you would need to use a larger change point (z>>2) before using
the second formula.

That asymptotic expansion can be converted to to a continued fraction,
Abramowitz & Stegun equation 7.1.14
http://jove.prohosting.com/~skripty/page_298.htm
that is absolutely convergent for all positive z. You would switch
between your first formula and the continued fraction around z=2.

Jerry

 

[Guest]

Dear Jerry,

Still I could not calculate NORMDIST. Here is my problem again,

NORMDIST(0.3,4,1.5,TRUE)=NORMSDIST((0.3-4)/1.5)=NORMSDIST(-2.46667)=(1-Efr(-(-2.466666)/(SQRT2)))/2=(1-Erf(1.744196))/2

Erf(1.744196)=1-(e^-(1.744196)^2)/(SQRT(pi))*(1/1.744196 –
(1/2)*(1/(1.744196)^3) + (3/4)*(1/(1.744196)^5) –
(15/8)*(1/(1.744196)^7)=0.986878

Then with this Erf=0.986878, the NORMSDIST(-2.46667) becomes 0.006560. But
it should be 0.006819. For this result the Erf should be 0.986362 which is
not giving by the above formula.

I have also tried with continued fraction method to calculate Erf which is
as follows.

Erf(1.744196)=1/1.744196+ (1/2)/1.744196+ 1/1.744196+ (3/2)/1.744197+
2/1.744196+ = 0.508553 and this is quite different from the above one.

Would you please let me know what is wrong with the above calculations?


Regards

Rijan

 

[Jerry W. Lewis]

I get 0.986876 instead of the 0.986878 that you report. Recall that I
said that this was an assymptotic series that will not converge, but
could be used for a reasonable approximation when z>>2 but you have
tried to use it for 1.744196 < 2. It is an alternating series, so
successive sums give you alternately upper an lower bounds. If you stop
the sum at the previous term, you get 0.985848. Thus all you can get
with this assymptotic series is that
0.985848 < erf(1.744196) < 0.986876
which is true, but not a very precise approximation.

Summing through n=18 on Abramowitz & Steguns 7.1.5 (your first formula)
gives 0.986362275 which is correct to 9 figures.

Jerry

 

[Guest]

Dear Jerry W. Lewis,

Thank you very much for your kind reply. It was my mistake choosing a right
equation before. Using Abramowitz & Steguns 7.1.5, I can calculate precise
Erf. My problem to calculate NORMDIST is solved. Thanks a lot!

Regards,

Rijan

 

[Dana DeLouis]

I have also tried with continued fraction method to calculate Erf which is

as follows.

Erf(1.744196)=1/1.744196+ (1/2)/1.744196+ 1/1.744196+ (3/2)/1.744197+
2/1.744196+ = 0.508553 and this is quite different from the above one.

Hi. I see that you have it working. Just for feedback, in the continued
fraction equation, you had it correct, you were just missing the leading
part of the equation. Instead of writing it out long form, here is a custom
function. You should see that part you were missing.

Function ERF(z)
'// ============================
'// Continued Fraction
'// ============================
Dim Num
Dim t
Dim tt

t = z
For Num = 100 To (1 / 2) Step -(1 / 2)
t = z + Num / t
Next Num
t = 1 / t

'// You were missing this part...
tt = Exp(-(z ^ 2)) / Sqr(WorksheetFunction.Pi)
ERF = 1 - tt * t
End Function

?ERF(1.744196)
0.98636223631017

Which is pretty accurate according to another math program...

Erf[1.744196]
0.9863622363101697

Hope this helps. :>)

 

[Jerry W. Lewis]

You're welcome. Glad it helped.

Jerry

 

[luinox86]

Hi guys, I know a long time has passed from this thread, I hope you still can receive emails about it. I am facing the exact problem Rijan was facing. You said your problem is solved but I dont know how, can anyone of you please help me out here?

I want the exact formula for Excel 2007 NormsDist. By the time being I am reading Abramowitz & Steguns 7.1.5 To find the solution. Thanks guys.