Excel NORMSINV (Table of Contents)
Introduction to Excel NORMSINV
NORMSINV function in Excel is used to calculate the probability of inverse normal cumulative distribution, which has a mean and standard deviation. Normsinv function can be seen as Norm.S.Inv. To find NORMSINV, first, we need to calculate the Normal Distribution of; we must have X, Mean, Standard Deviation. Once we get the value of Normal Distribution, we can easily calculate NORMSINV using the probability we got as per syntax.
Syntax of Excel NORMSINV
Argument:
Probability – Which is nothing but probability corresponds to the normal distribution.
How to Use NORMSINV Formula in Excel?
In Microsoft excel, the NORMSINV built-in function is categorized under statistical function, which is shown in the below screenshot (where it will calculate the inverse of the normal cumulative distribution for a given probability).
- Go to Formulas Menu.
- Click More Function as shown in the below screenshot.
- Choose a Statistical category under that we will find the NORM.DIST function is shown below.
Example #1 – Using NORM.DIST and NORMSINV
In order to use NORM.DIST function let’s start with an easy example where we need to find out the Students Grades; suppose we have the class exam with an average grade of 70, i.e. mu=70 and class standard deviation is 3 Points, i.e. sigma=3 here we need to find out what is the probability that students got the marks 73 or below, i.e. P(X<=73). So let’s see how to find out the probability using the NORM.DIST function.
- X=3
- Mean=70
- Standard Deviation=3
- Apply the NORM.DIST function as below.
- If we apply the above NORM.DIST function, we will get the probability of 0.0807.
- Now apply the NORMSINV function to find out the inverse of the normal cumulative distribution as shown below.
Result –
In the below result, we can see that we got negative values -1.40067 for the given probability, i.e. the inverse of normal cumulative distribution.
Example #2 – Mean and Exact Standard Deviation
Let’s see another example with curve-based data so that we can get to know the mean and exact standard deviation.
- Mean =7
- Standard Deviation=1.3
- Standard Deviation Increment as -3
- To get the bell curve, we have to add a 0.1 to standard deviation increment where the data is shown below.
- After applying the formula, the result is as shown below.
- Drag the values to get more values until we get the positive values to get a left curve.
- To get the Right curve, we have to apply the formula as =mean-standard deviation * 3 so that we will get the exact curves.
- After using the formula, the result is shown below.
- As in the above data for standard deviation increment to get the left curve, we have incremented the values by 0.1
- The same scenario is used by applying the formula as =3.1+STANDARD DEVIATION/10 to get the curve increment of 0.1
- After using the formula, the result is shown below.
- Drag the values to get the exact result which is shown in the below screenshot.
- Now apply the normal distribution function with the formula = NORM.DIST(DATA value, mean, standard deviation, false).
- We will get the below result as follows.
- Drag the values to get the exact result which is shown below.
- As we can see in the above screenshot, we have calculated the NORMAL distribution from the mean and standard deviation. Now let’s see what will be the inverse of NORMAL distribution by applying the NORMSINV, which is shown below.
- Here we can see that Value Zero (0) has a standard deviation of 7.
Applying scattered graph to take a look at how the left and right curve appears.
- First, select the data and the Normal column.
- Go to the Insert tab and select the scattered graph as follows.
- We will get the below curve graph as shown below.
Here we can see that Mean value 7 has a standard deviation shape where we can show that by drawing a straight line to represent it.
- Mean =7
- 1 –Standard deviation indicates 68% of Data.
- 2 –Standard deviation indicates 95% of Data.
- 3 –Standard deviation indicates 99.7% of Data.
Normal Distribution Graph:
NORMSINV Graph:
From the above figure, select the data column and NORM SINV to get the below graph.
- First, select the data and the Normal column.
- Go to the Insert tab and select the scattered graph.
- We will get the below graph which is shown in the below screenshot.
- From the above screenshot, we can see that we got an exact inverse of a normal distribution which shows the same value figure shown below.
Example #3 – Configuring the Left and Right Curve
In this example, we will configure the left and right curve using the normal distribution function. Consider the below data as shown below where x has negatives values, and it gets incremented to positive values.
- Apply the formula =NORM.DIST(A2,0,1,1).
- After applying the formula, the result is shown below.
- Drag the formula in other cells.
- Apply formula =1-B2.
- After applying the formula, the result is shown below.
- Drag the same formula in other cells.
The result of the above-applied formula is shown below.
- Left curve values have been calculated by applying the NORMAL DISTRIBUTION formula by setting the cumulative value as True, and the NORMSINV has been calculated using the left curve.
- After applying the formula, the result is shown below.
- Drag the same formula in other cells.
As we can see that we got the same value for NORMSINV, which is nothing but the inverse of the normal distribution. In the same way, we will get the right curve value by calculating the 1-left curve value. In the next step, we are going to check how we will get the height of the x by using the scattered graph.
- Select the left cure and right curve column.
- Go to insert menu.
- Select the scattered graph as follows.
We will get the below graph result as shown below.
NORM SINV Graph:
In the below graph, we can see that the NORM DISTRIBUTION value left curve has the exact match for (0, 0.5 ), which lies at the center of the line where we will get the same graph if we apply for NORMDIST.
Here in the above graph, it shows very clearly that we got the exact mean at a center point which denotes:
- X=0
- Left Curve=0.5
- Right Curve=0.5
We displayed it to view the NORMSINV values in a graphical format, as shown below.
Things to Remember About Excel NORMSINV
- #value! The error occurs when the given argument is a non-numeric or logical value.
- In the Normal Distribution function, we usually get #NUM! error due to the standard deviation argument is less than or equal to zero.
Recommended Articles
This is a guide to Excel NORMSINV. Here we discuss how to use NORMSINV in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
