Excel NORMSINV (Table of Contents)
Introduction to Excel NORMSINV
The Inverse normal cumulative distribution function in excel is an important tool which returns the inverse normal cumulative distribution for a given probability value i.e. it normally returns the inverse of the standard normal cumulative distribution (which has a mean of zero and a standard deviation of one) The NORM.S.INV function is first introduced in Microsoft excel version 2010 which is an updated version of the NORMSINV function in excel 2013 and the latest version. NORMSINV function is mostly used in accounts payable and finance analysis.
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 as 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 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 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 0.1 to standard deviation increment where the data is as shown below.
- After applying the formula result is as shown below.
- Drag the values to get more values till we get the positive values so that we will 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 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 0.1
- After using the formula 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 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 as 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 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:
Now from the above figure select the data column and NORM SINV to get the below graph as follows.
- First, select the data and the Normal column.
- Go to 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 result is shown below.
- Drag the formula in other cells.
- Apply formula =1-B2.
- After applying 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 has 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 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 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! Error occurs when the given argument is non-numeric or logical value.
- In 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 downloadable excel template. You can also go through our other suggested articles –
