Finding a Z value in Excel using the NORM.S.INV function

The z value (also called a z-score) measures how many standard deviations a data point sits away from the average. Excel calculates it using the NORM.S.INV function, which takes a probability between 0 and 1 and returns the z value that corresponds to it. For most work, you will use this function when you already know a probability and need to find the z value that matches it.

The basic formula is =NORM.S.INV(probability). If you want the z value for a probability of 0.95, you type =NORM.S.INV(0.95) into any cell and press Enter. Excel returns 1.645, meaning that 95 percent of your data falls below 1.645 standard deviations above the mean.

This is different from calculating a z value from raw data, which requires a separate approach. Most people either need to find a z value from a known probability (using NORM.S.INV) or calculate a z value from a single data point (using a formula with AVERAGE and STDEV). This section covers the first task; the next section covers the second.

Key Takeaways

  • Use NORM.S.INV when you know a probability and want to find the z value that matches it, such as finding the z value for the 95th percentile.
  • The formula =NORM.S.INV(0.95) returns 1.645, meaning 95 percent of data falls below that point in a standard normal distribution.
  • To calculate a z value from a single data point, use the formula =(data point - AVERAGE(range)) / STDEV(range).
  • Excel's NORM.S.INV only works with probabilities between 0 and 1; entering 0.95 is correct, but entering 95 will return an error.

Using NORM.S.INV to find z values from probabilities

Open your spreadsheet and click on any empty cell. Type the formula =NORM.S.INV(0.95) and press Enter. The cell now shows 1.645. This means that if you have a normal distribution, 95 percent of all values fall below the point that is 1.645 standard deviations above the mean.

You can change the probability to any decimal between 0 and 1. For the 90th percentile, use 0.90. For the 99th percentile, use 0.99. For the median (50th percentile), use 0.5, and Excel returns 0 — because the median is zero standard deviations away from the mean. Probabilities below 0.5 return negative z values, which represent points below the mean.

If you have a column of probabilities and want to find the z value for each one, put the formula in the first cell next to your data, then copy it down. Click the cell with your formula, copy it (Ctrl+C on Windows, Cmd+C on Mac), select the range where you want the results, and paste (Ctrl+V or Cmd+V). Excel adjusts the cell reference automatically for each row.

Calculating a z value from a single data point

If you have a data point and want to know how many standard deviations it is from the average, use a different approach. The formula is =(data point - AVERAGE(range)) / STDEV(range). Suppose your data is in cells A2 through A100, and you want the z value for the number in cell A5. Type =(A5-AVERAGE($A$2:$A$100))/STDEV($A$2:$A$100) into an empty cell and press Enter.

The dollar signs ($) lock the range so that if you copy the formula down, the average and standard deviation always refer to the same cells. Without them, the range shifts as you copy, and your results become wrong. The data point reference (A5) does not have dollar signs, so it changes to A6, A7, and so on as you copy down.

This formula works for any distribution of data. It tells you whether a value is typical (z value close to 0), unusual (z value between 2 and 3), or an outlier (z value above 3 or below -3). A z value of 2 means the data point is 2 standard deviations above the mean; a z value of -1.5 means it is 1.5 standard deviations below the mean.

Understanding the difference between the two approaches

NORM.S.INV answers the question: "What z value corresponds to this probability?" You use it when you are working with percentiles or confidence levels. For example, a 95 percent confidence interval uses a z value of 1.96, which you can find by typing =NORM.S.INV(0.975) (note 0.975, not 0.95, because the function measures one tail of the distribution).

The manual formula answers the question: "How many standard deviations is this data point from the average?" You use it when you have actual numbers and want to standardize them or identify outliers. Both are called z values, but they come from different calculations and serve different purposes.

If you are unsure which one you need, ask yourself: Do I have a probability or percentile? If yes, use NORM.S.INV. Do I have a data point and a dataset? If yes, use the manual formula. Many people mix these up, so checking your source material or the context of your work helps you pick the right one.

Common mistakes when finding z values

The most common error is entering a percentage instead of a decimal. If you type =NORM.S.INV(95) instead of =NORM.S.INV(0.95), Excel returns an error or a nonsensical result because it expects a number between 0 and 1. Always convert percentages to decimals: 95 percent becomes 0.95, 50 percent becomes 0.5, and 99.5 percent becomes 0.995.

Another mistake is forgetting the dollar signs when copying a formula down. If you type =(A5-AVERAGE(A2:A100))/STDEV(A2:A100) and copy it to the next row, the range shifts to A3:A101, which is wrong. Always use =$A$2:$A$100 to keep the range fixed.

A third mistake is using STDEV.S instead of STDEV or using STDEV.P when you should use STDEV.S. STDEV.S calculates the sample standard deviation (used most often), while STDEV.P calculates the population standard deviation (used only when your data represents the entire population, not a sample). For most work, use STDEV.S or the older STDEV function, which does the same thing.

Checking your z value results

If you used NORM.S.INV, you can verify your result by using the NORM.S.DIST function in reverse. Type =NORM.S.DIST(1.645, TRUE) into a cell. It returns 0.95, confirming that a z value of 1.645 corresponds to the 95th percentile. The TRUE argument tells Excel to return the cumulative probability (the percentage of data below that point) rather than the probability density.

If you calculated a z value from a data point, check it by hand: subtract the average from your data point, then divide by the standard deviation. If your formula gives a different result, check that you used the correct cell references and that you locked the range with dollar signs.

You can also sanity-check the size of your z value. Values between -3 and 3 are normal; anything beyond that is unusual. If you get a z value of 50 or -50, something went wrong — either your data has an error, your range is wrong, or your formula is incorrect.

Frequently Asked Questions

What is the difference between NORM.S.INV and NORM.INV?

NORM.S.INV works with the standard normal distribution, which always has a mean of 0 and a standard deviation of 1. NORM.INV lets you specify a different mean and standard deviation. For most work, use NORM.S.INV. If your data has a mean of 100 and a standard deviation of 15, and you want the value at the 95th percentile, use NORM.INV(0.95, 100, 15) instead.

Can I use z values to find probabilities in Excel?

Yes, use NORM.S.DIST. Type =NORM.S.DIST(1.645, TRUE) to find the probability that corresponds to a z value of 1.645. It returns 0.95, or the 95th percentile. The TRUE argument gives you the cumulative probability; FALSE gives you the probability density, which is rarely useful for this task.

What does a negative z value mean?

A negative z value means the data point is below the average. A z value of -2 means the point is 2 standard deviations below the mean. A z value of -1.5 means it is 1.5 standard deviations below the mean. The distance from the mean is what matters; the sign just tells you which direction.

Why do I get an error when I use NORM.S.INV?

The most common cause is entering a number outside the range 0 to 1. Check that you typed a decimal (0.95) and not a percentage (95). Also check that you did not accidentally type a number greater than 1 or less than 0. If your formula looks correct, make sure you are using NORM.S.INV and not a different function name.

Should I use STDEV or STDEV.S for my z value calculation?

Both give the same result. STDEV is the older function name, and STDEV.S is the newer one. Excel still supports both, so either works. Use whichever one your workplace or textbook uses. If you are starting fresh, STDEV.S is clearer because the .S reminds you it is the sample standard deviation.