Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula to determine number of Standard Deviations based on % of population
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. |
#2
|
|||
|
|||
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%. |
#3
|
|||
|
|||
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. -- Regards, Dave <!-- "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 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. |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 wrote: 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 |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
inv_normal is a more accurate implementation of NORMSINV. Use Harlan's
formula with either. Jerry Paul D. Simon wrote: 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. |
#9
|
|||
|
|||
Now I see. Works perfectly. Thanks very much, Jerry.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Evaluating Rank to determine formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
PivotTables in Excel 2004 calculate incorrect standard deviations | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |