NORM.INV Function in Google Sheets: Complete Guide

Key Insights

NORM.INV converts a probability into the corresponding value on a normal distribution, making it essential for confidence intervals, risk analysis, and simulation work.
The function requires three parameters—probability, mean, and standard deviation—and returns the x-value where the cumulative distribution equals your specified probability.
Pair NORM.INV with AVERAGE and STDEV functions to build dynamic statistical models that automatically update as your data changes.

Introduction to NORM.INV

The NORM.INV function answers a fundamental statistical question: “Given a probability, what value on my normal distribution corresponds to that probability?” This is the inverse of the more common question NORM.DIST answers, which is “Given a value, what’s the probability?”

Think of it this way. If you know that 95% of your data falls below a certain threshold, NORM.INV tells you exactly what that threshold is. The “inverse” in the name refers to this reversal—you’re working backward from probability to find the actual value.

This function is indispensable when you need to establish cutoff points, calculate confidence intervals, or generate normally distributed random values for simulations. Rather than looking up values in statistical tables, NORM.INV gives you precise answers instantly.

Syntax and Parameters

The function follows this structure:

=NORM.INV(probability, mean, standard_deviation)

Let’s break down each parameter:

probability (required): A value between 0 and 1 (exclusive) representing the cumulative probability. This is the area under the normal curve to the left of the value you’re seeking. A probability of 0.5 returns the mean, 0.95 returns the 95th percentile, and so on.

mean (required): The average of your normal distribution. This can be any real number—positive, negative, or zero.

standard_deviation (required): The spread of your distribution. This must be a positive number. A larger standard deviation means more spread; smaller means tighter clustering around the mean.

Here’s a basic demonstration:

=NORM.INV(0.5, 100, 15)

This returns 100. When you ask for the 50th percentile (probability = 0.5), you always get the mean back. The result makes intuitive sense—half the values fall below the average.

=NORM.INV(0.84, 100, 15)

This returns approximately 115, which is one standard deviation above the mean. The 84th percentile of a normal distribution sits at roughly μ + σ.

Understanding the Output

The value NORM.INV returns is the x-coordinate on a normal distribution curve where the cumulative probability from negative infinity to that point equals your specified probability.

Consider the standard normal distribution (mean = 0, standard deviation = 1):

=NORM.INV(0.5, 0, 1)

Returns: 0

This confirms that half the probability mass lies below zero (the mean) in a standard normal distribution.

=NORM.INV(0.975, 0, 1)

Returns: 1.959963985 (approximately 1.96)

This is the famous z-score used in 95% confidence intervals. Since 97.5% of values fall below 1.96, and by symmetry 2.5% fall below -1.96, the middle 95% lies between these bounds.

=NORM.INV(0.025, 0, 1)

Returns: -1.959963985

The lower bound mirrors the upper bound, demonstrating the normal distribution’s symmetry.

Visualize this on a bell curve: the probability you input represents the shaded area from the left tail up to some point. NORM.INV tells you where that point is.

Practical Use Cases

Calculating Confidence Interval Bounds

The most common application is building confidence intervals. Suppose you have sample data and want to construct a 95% confidence interval for the population mean.

=NORM.INV(0.025, A1, B1)   // Lower bound
=NORM.INV(0.975, A1, B1)   // Upper bound

Where A1 contains your sample mean and B1 contains the standard error (standard deviation divided by the square root of sample size).

For a complete confidence interval calculation with raw data:

// Assuming data in A2:A101 (100 observations)
// Cell D2: Sample Mean
=AVERAGE(A2:A101)

// Cell D3: Standard Error
=STDEV(A2:A101)/SQRT(COUNT(A2:A101))

// Cell D4: Lower 95% CI Bound
=NORM.INV(0.025, D2, D3)

// Cell D5: Upper 95% CI Bound
=NORM.INV(0.975, D2, D3)

Risk Analysis and Value at Risk (VaR)

Financial analysts use NORM.INV to calculate Value at Risk—the maximum expected loss at a given confidence level.

// Portfolio value: $1,000,000
// Daily return mean: 0.05% (0.0005)
// Daily return std dev: 2% (0.02)
// 95% VaR calculation

=1000000 * NORM.INV(0.05, 0.0005, 0.02)

This returns approximately -$32,400, meaning there’s a 5% chance of losing more than $32,400 in a single day.

Quality Control Thresholds

Manufacturing uses NORM.INV to set specification limits. If a process produces parts with a mean diameter of 50mm and standard deviation of 0.5mm:

// Lower specification limit (0.1% defect rate)
=NORM.INV(0.001, 50, 0.5)   // Returns ~48.46mm

// Upper specification limit (99.9% acceptable)
=NORM.INV(0.999, 50, 0.5)   // Returns ~51.54mm

Monte Carlo Simulations

Generate normally distributed random values for simulations by combining NORM.INV with RAND():

=NORM.INV(RAND(), 100, 15)

Each recalculation produces a new random value from a normal distribution with mean 100 and standard deviation 15.

Google Sheets offers several normal distribution functions. Understanding when to use each prevents confusion.

NORM.S.INV: The standard normal inverse. It assumes mean = 0 and standard deviation = 1, so it only takes one parameter (probability).

=NORM.S.INV(0.975)           // Returns 1.96
=NORM.INV(0.975, 0, 1)       // Returns 1.96 (identical result)

Use NORM.S.INV when working with z-scores directly. Use NORM.INV when your distribution has a different mean or standard deviation.

NORM.DIST: The forward calculation. Given a value, it returns the probability.

=NORM.DIST(1.96, 0, 1, TRUE)   // Returns 0.975
=NORM.INV(0.975, 0, 1)         // Returns 1.96

These are mathematical inverses of each other. NORM.DIST answers “what probability corresponds to this value?” while NORM.INV answers “what value corresponds to this probability?”

NORMINV: The legacy version of NORM.INV, included for compatibility with older spreadsheets. The syntax is identical. Use NORM.INV in new work—it’s the current standard.

=NORMINV(0.95, 50, 10)    // Legacy
=NORM.INV(0.95, 50, 10)   // Current (use this)

Both return 66.449, but stick with NORM.INV for consistency with modern documentation and formulas.

Common Errors and Troubleshooting

#NUM! Error

This appears when parameters fall outside acceptable ranges:

=NORM.INV(1.5, 0, 1)     // #NUM! - probability > 1
=NORM.INV(-0.1, 0, 1)    // #NUM! - probability < 0
=NORM.INV(0, 0, 1)       // #NUM! - probability = 0 (must be > 0)
=NORM.INV(1, 0, 1)       // #NUM! - probability = 1 (must be < 1)
=NORM.INV(0.5, 0, -1)    // #NUM! - negative standard deviation

#VALUE! Error

Non-numeric inputs trigger this error:

=NORM.INV("high", 0, 1)   // #VALUE!
=NORM.INV(0.5, "mean", 1) // #VALUE!

Error-Handling Wrapper

Build robust formulas with IFERROR:

=IFERROR(
  NORM.INV(A1, B1, C1),
  "Check inputs: probability must be 0-1, std dev must be positive"
)

For more granular error handling:

=IF(OR(A1<=0, A1>=1), "Probability must be between 0 and 1",
  IF(C1<=0, "Standard deviation must be positive",
    IF(OR(NOT(ISNUMBER(A1)), NOT(ISNUMBER(B1)), NOT(ISNUMBER(C1))),
      "All inputs must be numbers",
      NORM.INV(A1, B1, C1)
    )
  )
)

Advanced Applications

Dynamic Percentile Calculator

Create a flexible tool that calculates any percentile from your dataset:

// Data in A2:A1000
// Desired percentile in D1 (e.g., 0.90 for 90th percentile)

=NORM.INV(D1, AVERAGE(A2:A1000), STDEV(A2:A1000))

This formula automatically updates as your data changes, always returning the current percentile value based on the actual mean and standard deviation.

Multiple Percentiles with Array Formula

Calculate several percentiles simultaneously:

// Percentiles in E2:E6 (0.10, 0.25, 0.50, 0.75, 0.90)
// Data in A2:A1000

=ARRAYFORMULA(NORM.INV(E2:E6, AVERAGE(A2:A1000), STDEV(A2:A1000)))

This populates five cells with the 10th, 25th, 50th, 75th, and 90th percentiles.

Confidence Interval Dashboard

Build a dynamic confidence interval calculator:

// Data range: A2:A500
// Confidence level in G1 (e.g., 0.95)

// Cell G2: Alpha
=(1-G1)/2

// Cell G3: Lower Bound
=NORM.INV(G2, AVERAGE(A2:A500), STDEV(A2:A500)/SQRT(COUNT(A2:A500)))

// Cell G4: Upper Bound
=NORM.INV(1-G2, AVERAGE(A2:A500), STDEV(A2:A500)/SQRT(COUNT(A2:A500)))

Change the confidence level in G1, and both bounds recalculate instantly.

Simulation Template

Generate multiple simulation runs:

// In cells B2:B1001, enter this formula and fill down:
=NORM.INV(RAND(), $D$1, $D$2)

Where D1 contains your target mean and D2 contains your target standard deviation. Press F9 to regenerate all 1000 random values. This forms the foundation for Monte Carlo analysis in Google Sheets.

The NORM.INV function bridges probability and real-world values. Master it, and you unlock powerful statistical analysis capabilities directly in your spreadsheet—no specialized software required.