Normal Distribution in Excel: Finding Area
Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.
A Normal Distribution Curve
Normal Distribution
The normal distribution is a very common continuous probability distribution in probability theory. The normal distribution is often referred to as the bell curve even though other distributions have a bell-shaped curve as well. This distribution is useful because properties of the central limit theorem allow for its use in many applications.
Excel Normal Distribution Function (NORM.DIST Function)
The normal distribution function displays the normal distribution when a specified mean, standard deviation, x value, and function determination are added to the function. The use of this function is found mostly in statistics to determine the area in a normal distribution. Hypothesis testing, which is in the inferential area of statistics, is where you will find wide use of this function. A description of the NORM.DIST syntax is displayed in the table below.
Syntax For The Normal Distribution Function
=NORM.DIST(x,mean,standard_dev,cumulative) |
---|
x = The value to be tested |
mean = The arithmetic mean of the distribution |
standard_dev = The standard deviation of the distribution |
cumulative = FALSE or zero, is the prob. that x will occur |
cumulative = TRUE or non-zero, is the prob. of less than or equal to x will occur |
Left Tail Example
Let's just say for example that a company has an average sales value of 2.5 million dollars. Also, let’s suppose that sales are normally distributed with a standard deviation of .5 million and we want to know the percentage of sales below 3.5 million. When we plug our numbers into a cell =NORM.DIST(3.5,2.5,.5,true) we get .977 as a result. True is selected for the type of function because we want to see the cumulative distribution up to the point of 3.5. Since the example states that we want to find the percentage of sales below or less than 3.5, we know we are calculating the percentage in the left tail. When this function is used to calculate the left tail no further calculations is permitted after the NORM.DIST function is used.
Excel Calculation - Left Tail Example
Normal Distribution - Left Tail Area
Right Tail Example
This example is similar to the left tail example because the same data is being used. The average sales value is 2.5 million dollars, sales is normally distributed, the standard deviation is .5 million dollars and the value of x is 3.5 million dollars. The difference here is that we want to find the percentage of sales above 3.5 million. Essentially, to be able to find this value, we must find the same cumulative distribution as the previous example and subtract it from 1. So, the function appears like this: =1-NORM.DIST(3.5,2.5,.5,true). Since we are looking for everything in the right tail (greater than 3.5), all that needs to be calculated is the complement of the left tail.
Excel Calculation - Right Tail Example
Normal Distribution - Right Tail Area
Center Area Example
The center area may seem tricky to calculate to some people. In this example, let's say we are are still using 2.5 million dollars for average sales, sales is normally distributed, and the standard deviation is .5 million dollars. The only difference now is that we need to find the percentage of sales between 2 and 3.5 million dollars. By looking at the previous distributions illustrations above can you figure out how to find the center area? You simply must find the area up to the 3.5 million dollars, then subtract that area by the area that appears below 2. The operation of the function in this case looks like this =NORM.DIST(3.5,2.5,.5,true)-NORM.DIST(2,2.5,.5,true). This is really self-explanatory. By subtracting the whole area to the left of 3.5 by the area to the left of 2 you will be left with the center.
Excel Calculation - Center Area Example
Normal Distribution - Center Area
To learn more about completing statistical calculations in Excel I suggest that you try reading the Excel Bible. This a good reference book form beginner users of Excel.
The Excel 2019 Bible
References
- NORM.DIST function. (n.d.). Retrieved October 15, 2018, from https://support.office.com/en-us/article/norm-dist-function-edb1cc14-a21c-4e53-839d-8082074c9f8d
- Normal distribution. (2018, October 10). Retrieved October 15, 2018, from https://en.wikipedia.org/wiki/Normal_distribution
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2018 Joshua Crowder