Question about NORM.DIST function

Question

Hi all,

I was wondering where to find technical information on the NORM.DIST function.  In particular, I'm wondering what numerical method they use to compute the integral in, say,  =NORM.DIST(2, 0, 1, TRUE)

The reason I ask is that I want to give my students a way to compute the normal table out to as much precision as they want and I'm not sure how many digits of the Excel result I can 'trust'.  Excel will display out to the 9th decimal place, but I don't know if that's accurate.  I am curious whether they are actually performing the quadrature with good enough error bounds or if they're just interpolating between values in a pre-calculated table.  Based on the speed that it can calculate several hundred results of NORM.DIST, I'm guessing the latter, but I'd like to know for sure.  

Thanks in advance!

Joe

Answer 1

> ... I want to give my students a way to compute the normal table out to as much precision as they want

Ahh.  I learned something.   I showed the op the loop earlier, but he wasn't interested in it.

Anyway, for my own education, I noticed that the technique to approach the last few digits possible in Excel was still based on reaching an error value.  I showed a technique for bumping it up a little.

However, by grouping the coefficients in 2's, and re-writing the equation, we can have the large numbers approach limit 0 instead of large errors.

So here, we only do 16 loops.  Loops 17 & 18 are there to show it converged and not generating any errors.

We then take the input value of 2, along with Pi, and do 1 Sqrt instead of 2.

This new technique gets us to within 1 digit.

Loop / T

0   0.3333333333333333333333333333

 1   0.5428571428571428571428571428

 2   0.5926887926887926887926887926

 3   0.5978332778332778332778332777

... etc

 14  0.5981440066613041014657118226

 15  0.5981440066613041014657118848

 16  0.598144006661304101465711885

 17  0.598144006661304101465711885

 18  0.598144006661304101465711885

Debug.Print 0.5 + T * sr

 0.9772498680518207927997173627

vs actual:

 0.97724986805182079279971736283...

That's about as close as we can get with Excel's vba.

Again, this was more for my own education.

Answer 2

JoeYoest wrote: I am curious whether they are actually performing the quadrature with good enough error bounds or if they're just interpolating between values in a pre-calculated table.

Unless Microsoft has documented the internal algorithm -- which is unlikely -- no one in this peer-to-peer forum can answer the question dispositively.  Only an Excel engineer would know. AFAIK, they never participate in this forum. And if they did, they are probably not permitted to divulge such proprietary information.

---

JoeYoest wrote: I'm not sure how many digits of the Excel result I can 'trust'.  Excel will display out to the 9th decimal place, but I don't know if that's accurate.

In a nutshell, we cannot say, a priori, how many decimal digits of the result of Excel NORM.DIST can be "trusted".

There is a difference between "accuracy" and "precision".

We cannot know how "accurate" the Excel NORM.DIST results are without comparing them to a third-party implementation that can be considered a benchmark.

For example, Excel PI displays the value 3.14159265358979. The exact decimal representation of the binary value is 3.14159265358979,3115997963468544185161590576171875. But one website [3] says that pi to the same number of decimal places is actually 3.14159265358979,3238462643383279502884197169399375.

(I use period for the decimal point and comma to demarcate the first 15 significant digits.)

So we might say that Excel PI is accurate to 15 decimal places (16 significant decimal digits).  But that is an accident of implementation [4].  And note that Excel formats only up to 15 significant digits (rounded), anyway -- 14 decimal places for this example.

On the other hand, all Excel numeric values are "precise" to 53 binary digits -- that is, the sum of 53 consecutive powers of 2 times an exponential factor, which can be represented by 1 to 716 [1] significant decimal digits.

But the "accuracy" of such precision is an illusion. To demonstrate, consider the decimal fraction 0.23.

By definition, it is "accurate" to 2 decimal places.

But the exact decimal representation of the "precise" binary value is 0.230000000000000,0099920072216264088638126850128173828125.

Moreover, note that =(100.23 - 100 = 0.23) returns FALSE(!) [2].

The reason is: the binary representation of the decimal fraction part of 100.23 is 0.230000000000003,9790393202565610408782958984375. 

The difference arises because some of the 53 consecutive powers of 2 must be used to represent the integer part (100), leaving the sum of fewer powers of 2 to approximate the decimal fraction part.

---

[1] The exact decimal representation of the smallest (normalized) binary value, 2^-1022, is 1023 decimal digits after the decimal point; that is, 307 zeros followed by 716 decimal digits.

[2] We can minimize such anomalies by explicitly rounding to the accuracy that we expect. For example,

=(ROUND(100.23 - 100**, 2**) = 0.23).

[3] I did not vet the website that provides the first 100,000 digits of pi.

[4] The exact decimal representation of the binary value for 3.14159265358979 is 3.14159265358979,0007373494518105871975421905517578125.

But the exact decimal representation of the binary value for 3.1415926535897932 is

3.14159265358979,3115997963468544185161590576171875.

So that is as close to the 17-decimal-digit representation of pi that we can get with 64-bit binary floating-point.

We can enter 3.1415926535897932 into Excel with the formula =3.14159265358979+32E-16. Or we can use VBA to return CDbl("3.1415926535897932"). VBA processes all digits, not just the first 15 significant digits, as Excel does.

Answer 3

Virgil wrote:

1)Open Excel>Home> On the Number tab, under Category, click more Number Formats.

2)Under the Category choose Number, set Decimal places as max value:30 and click OK.

Although Excel permits us to display up to 30 decimal places, Excel only formats the first 15 significant digits (rounded).

For example, the exact decimal representation of the binary value returned by NORM.DIST(2,0,1,TRUE) is

0.977249868051820,79141474105199449695646762847900390625.

(I use period for the decimal point and comma to demarcate the first 15 significant digits.)

But Excel formats it with 30 decimal places as 0.977249868051821000000000000000.

So for this example, there is no point in formatting more than 15 decimal places.

Answer 4

Hi JoeYoest,

If you are using the formula: =NORM.DIST(2, 0, 1, TRUE), the return value can be accurate to 15 digits after the decimal point as the Dana D_ mentioned.

You can display the value by the following steps:

1)Open Excel>Home> On the Number tab, under Category, click more Number Formats.

2)Under the Category choose Number, set Decimal places as max value:30 and click OK.

3)Back to Excel select number On the Number tab and run the formula to check the outcome.

For more details about the NORM.DIST please refer to NORM.DIST function.

Regards,

Virgil