Formula to determine number of Standard Deviations based on % of population

[Paul D. Simon]

The following are a few of generally accepted values:

% of Population Number of Standard Deviations away from Mean
50.0% 0.674
68.3% 1.000
90.0% 1.645
95.0% 1.960
95.4% 2.000
99.7% 3.000


Given the % of Population, is there an Excel formula that can be used
to determine the number of Standard Deviations away from the Mean?
Thus, entering 90% in A1, the formula in B1 would result in 1.645 as
shown above.

I'd be using this to expand the table above to include additional
percentages (e.g., 75%, 85%, or whatever number I needed at the time).

Many thanks.

 

[Harlan Grove]

Paul D. Simon wrote...

The following are a few of generally accepted values:

% of Population Number of Standard Deviations away from Mean
50.0% 0.674
68.3% 1.000
90.0% 1.645
95.0% 1.960
95.4% 2.000
99.7% 3.000

....

This is just one-tailed critical values from the standard normal
distribution. Use

=NORMSINV((1+pct)/2)

Note, however, that prior to Excel 2002 the NORMSINV function did a
poor job with values in the tail, so as a pratical matter, above 97.5%.

 

[Guest]

If you already have the numbers calculated and listed in each column you can
use the VLOOKUP() function to pull the standard deviation from the
corresponding % in that row.

 

[Paul D. Simon]

Harlan,

Thanks very much for responding. Even though I'm using Excel 2000,
your formula seems to work perfectly, giving me all the known values
shown in the table above. So, I'm confident that I can use it to fill
in corresponding values for other percentages as well.

Thanks again - I appreciate it.

Paul

 

[Paul D. Simon]

Bill,

Thank you for responding, and sorry for the confusion. What I need to
do is fill in additional values not already included in the table
above. However, Harlan's response has done the trick for me. Thanks
again for responding, though - I appreciate it.

Paul

 

[Jerry W. Lewis]

Harlan was a bit conservative, since pre-2002 Excel gives 5-6 figure
accuracy on this calculation up to 99.99%, but it quickly degenerates
after that. You can get essentially machine accuracy from the
inv_normal function in
http://members.aol.com/iandjmsmith/Examples.xls
Jerry

 

[Paul D. Simon]

Thanks very much for your response, Jerry.

I tried the inv_normal function, but I couldn't figure out how to apply
it properly.

For example, the number of Standard Deviations for 68.3% is 1; for 95%,
it's 1.95996. When applying the inv_normal function to 68.3%, it gave
me an answer of 0.476104403489395. For 95%, it gave me an answer of
1.64485362695147. Obviously, I doing something wrong.

 

[Jerry W. Lewis]

inv_normal is a more accurate implementation of NORMSINV. Use Harlan's
formula with either.

Jerry

 

[Paul D. Simon]

Now I see. Works perfectly. Thanks very much, Jerry.