Normal distribution in Excel

Excel provides a built-in statistical function that allows users to calculate the normal distribution of a dataset based on a given mean and standard deviation. This is done using the NORM.DIST() function (known in French as LOI.NORMALE.N()), which was introduced in Excel 2010 as an improved version of the older NORMDIST() function.

The NORM.DIST() function can be used to return either the probability density function (PDF) or the cumulative distribution function (CDF) of a normal distribution, depending on the specified argument.

Syntax:

Arguments:

• x (Required):
  The value at which you want to evaluate the distribution. This is the random variable X.

• mean (Required):
  The arithmetic mean (µ) of the distribution.

• standard_dev (Required):
  The standard deviation (σ) of the distribution. It must be a positive number.

• cumulative (Required):
  A logical value (TRUE or FALSE) that determines the form of the function:

  • If TRUE, the function returns the cumulative distribution function (CDF), i.e., the probability that the variable is less than or equal to x.

  • If FALSE, the function returns the probability density function (PDF) at the given x.

Example Usage in Excel:

To calculate the cumulative probability that a value $x=70$ or less occurs in a normal distribution with a mean of 65 and a standard deviation of 10, use:

This returns the area under the normal curve to the left of x = 70.

To calculate the probability density at the exact value of 70, use:

This returns the height of the normal curve at $x=70$, rather than a cumulative probability.

This function is widely used in data analysis, hypothesis testing, and simulations to model and interpret normally distributed data. It is especially useful for evaluating statistical probabilities and making informed business or scientific decisions directly within Excel.

Example 1

We have stock data from an organization. The given stock price is 115, the overall average stock price is 90, and the standard deviation is 16.

We need to calculate the probability that the stock price is equal to or less than 115.

Let’s apply the NORM.DIST() function in Excel.

• X is the given stock price.

• Mean is the overall average price.

• Standard deviation is given in cell B4.

• The distribution type is set to “1”, which means TRUE (for cumulative distribution).

The result is 0.9409, which means that approximately 94% of the stock prices are less than 115.
In other words, the probability that the stock price is greater than 115 is less than 6%.

Now, let’s change the distribution type to FALSE (0) to apply the normal probability density function. We get the following result:

The NORM.DIST function returns the value 0.0074, indicating that 0.74% of the stocks have a price exactly equal to 115.

Example 2

Let’s consider the following data for a normal distribution in Excel:

• Population sample (X) = 200

• Mean or average value = 198

• Standard deviation = 25

Let’s apply the cumulative normal distribution in Excel.

The value of the normal distribution is 0.53188, which means the probability is 53.18%.

The NORM.INV Function (LOI.NORMALE.INVERSE.N in French Excel)

In practice, you will often need the NORM.INV function, especially when you have collected data and know the mean and standard deviation of a sample or population, but want to determine where a specific value lies within a normal distribution.

For example, the value might be a sample mean you want to compare to the population mean, or an individual observation you want to evaluate relative to a larger group.

When you input the information into NORM.DIST() (LOI.NORMALE.N()), it returns the relative probability of observing up to a certain value — either as a cumulative probability (if cumulative is TRUE) or as the probability density at that value (if cumulative is FALSE). You might compare this probability with the alpha level (false positive rate) or beta level (false negative rate) predefined for your experiment.

The NORM.INV() function complements NORM.DIST() by providing a different perspective:

• NORM.DIST() returns a probability value (area under the curve).

• NORM.INV() returns the value on the horizontal axis (the x-value) corresponding to a given cumulative probability.

In other words, the point returned by NORM.INV() is the inverse of the probability obtained from NORM.DIST().

Example:

returns 0.648, this means the value 60 is equal to or greater than 64.8% of the observations in a normal distribution with mean 54.3 and standard deviation 15.

Conversely,

returns 60 — meaning 64.8% of the distribution lies at or below the value 60.

Practical Usage Scenario:

Suppose in a research project you decide to conclude that a treatment effect is reliable only if the experimental group’s mean lies in the top 5% of the population of possible group means (which are normally distributed). This approach aligns with traditional hypothesis testing.

To find the cutoff score separating the top 5% from the bottom 95%, you use:

This returns 78.97, meaning that 5% of the normal distribution (mean 54.3, sd 15) lies above 78.97.

Note:

• The first argument to NORM.INV() is the cumulative probability (e.g., 0.95).

• Unlike NORM.DIST(), NORM.INV() does not require a cumulative argument because it always assumes cumulative probability.

Choosing Between NORM.DIST() and NORM.INV()

• Use NORM.DIST() if you want the probability that a value $X$ is less than or equal to a certain number.

• Use NORM.INV() if you want to know the value corresponding to a specific cumulative probability.

In both cases, you must provide the mean and standard deviation.

Standard Normal Functions: NORM.S.DIST() and NORM.S.INV()

Sometimes, you want to work with the standard normal distribution — a normal distribution with mean 0 and standard deviation 1.

• Use NORM.S.DIST(z, cumulative) to calculate the cumulative probability or probability density at a z-score $z$. For example,

returns approximately 0.933, meaning 93.3% of the area under the curve lies to the left of $z=1.5$.

• Use NORM.S.INV(probability) to find the z-score that corresponds to a given cumulative probability. For example,

=NORM.S.INV(0.95)

returns approximately 1.64, meaning 95% of the distribution lies below $z=1.64$.

These numbers are well-known in inferential statistics, commonly used in hypothesis testing (e.g., critical values corresponding to $p<0.05$).

Applications of the Normal Distribution

This mathematical function is applied across many fields, such as:

• Stock market technical analysis, where bell curves help investors assess expected returns and risks.

• Operations management, to optimize resource allocation and product efficiency.

• Population studies, for example, modeling heights where most individuals cluster around an average height.

• Education, to determine average student performance and rank individual scores.

• Data science and artificial intelligence, where normal density functions enhance machine learning and predictive models.

Summary of Excel Normal Distribution Functions