How to Use the NORM.INV Function in Excel

Statistical calculations are greatly sped up with the use of software. One way to do these calculations is by using Microsoft Excel. Of the variety of statistics and probability that can be done with this spreadsheet program, we will consider the NORM.INV function.

Reason for Use

Suppose that we have a normally distributed random variable denoted by x. One question that can be asked is, “For what value of x do we have the bottom 10% of the distribution?” The steps that we would go through for this type of problem are:

1. Using a standard normal distribution table, find the z score that corresponds to the lowest 10% of the distribution.
2. Use the z-score formula, and solve it for x. This gives us x = μ + zσ, where μ is the mean of the distribution and σ is the standard deviation.
3. Plug in all of our values into the above formula. This gives us our answer.

In Excel the NORM.INV function does all of this for us.

Arguments for NORM.INV

To use the function, simply type the following into an empty cell:

=NORM.INV(

The arguments for this function, in order, are:

1. Probability – this is the cumulative proportion of the distribution, corresponding to the area in the left-hand side of the distribution.
2. Mean - this was denoted above by μ, and is the center of our distribution.
3. Standard Deviation - this was denoted above by σ and accounts for the spread of our distribution.

Simply enter each of these arguments with a comma separating them. After the standard deviation has been entered, close the parentheses with ) and press the enter key. The output in the cell is the value of x that corresponds to our proportion.

Example Calculations

We will see how to use this function with a few example calculations. For all of these, we will assume that IQ is normally distributed with a mean of 100 and a standard deviation of 15. The questions we will answer are:

1. What is the range of values of the lowest 10% of all IQ scores?
2. What is the range of values of the highest 1% of all IQ scores?
3. What is the range of values of the middle 50% of all IQ scores?

For question 1 we enter =NORM.INV(.1,100,15). The output from Excel is approximately 80.78. This means that scores less than or equal to 80.78 comprise the lowest 10% of all IQ scores.

For question 2 we need to think a little before using the function. The NORM.INV function is designed to work with the left portion of our distribution. When we ask about an upper proportion we are looking at the right-hand side.

The top 1% is equivalent to asking about the bottom 99%. We enter =NORM.INV(.99,100,15). The output from Excel is approximately 134.90. This means that scores greater than or equal to 134.9 comprise the top 1% of all IQ scores.

For question 3 we must be even more clever. We realize that the middle 50% is found when we exclude the bottom 25% and the top 25%.

• For the bottom 25% we enter =NORM.INV(.25,100,15) and obtain 89.88.
• For the top 25% we enter =NORM.INV(.75, 100, 15) and obtain 110.12 

NORM.S.INV

If we are only working with standard normal distributions, then the NORM.S.INV function is slightly faster to use. With this function, the mean is always 0 and the standard deviation is always 1. The only argument is the probability.

The connection between the two functions is:

NORM.INV(Probability, 0, 1) = NORM.S.INV(Probability)

For any other normal distributions, we must use the NORM.INV function.