Standard and Normal Excel Distribution Calculations

Standard and Normal Excel Distribution Calculations Nearly any statistical software package can be used for calculations concerning a normal distribution, more commonly known as a bell curve.  Excel is equipped with a multitude of statistical tables and formulas, and it is quite straightforward to use one of its functions for a normal distribution.  We will see how to use the NORM.DIST and the NORM.S.DIST functions in Excel. Normal Distributions There is an infinite number of normal distributions. A normal distribution is defined by a particular function in which two values have been determined: the mean and the standard deviation. The mean is any real number that indicates the center of the distribution. The standard deviation is a positive real number that is a measurement of how spread out the distribution is. Once we know the values of the mean and standard deviation, the particular normal distribution that we are using has been completely determined. The standard normal distribution is one special distribution out of the infinite number of normal distributions. The standard normal distribution has a mean of 0 and a standard deviation of 1. Any normal distribution can be standardized to the standard normal distribution by a simple formula. This is why, typically, the only normal distribution with tabled values is that of the standard normal distribution. This type of table is sometimes referred to as a table of z-scores. NORM.S.DIST The first Excel function that we will examine is the NORM.S.DIST function. This function returns the standard normal distribution. There are two arguments required for the function: â€Å"z† and â€Å"cumulative.† The first argument of z is the number of standard deviations away from the mean. So,  z -1.5 is one and a half standard deviations below the mean. The z-score of z 2 is two standard deviations above the mean. The second argument is that of â€Å"cumulative.† There are two possible values that can be entered here: 0 for the value of the probability density function and 1 for the value of the cumulative distribution function. To determine the area under the curve, we will want to enter a 1 here. Example To help to understand how this function works, we will look at an example. If we click on a cell and enter NORM.S.DIST(.25, 1), after hitting enter the cell will contain the value 0.5987, which has been rounded to four decimal places. What does this mean? There are two interpretations. The first is that the area under the curve for z less than or equal to 0.25 is 0.5987. The second interpretation is that 59.87 percent of the area under the curve for the standard normal distribution occurs when z is less than or equal to 0.25. NORM.DIST The second Excel function that we will look at is the NORM.DIST function. This function returns the normal distribution for a specified mean and standard deviation. There are four arguments required for the function: â€Å"x,† â€Å"mean,† â€Å"standard deviation,† and â€Å"cumulative.† The first argument of x is the observed value of our distribution. The mean and standard deviation are self-explanatory. The last argument of â€Å"cumulative† is identical to that of the NORM.S.DIST function. Example To help to understand how this function works, we will look at an example. If we click on a cell and enter NORM.DIST(9, 6, 12, 1), after hitting enter the cell will contain the value 0.5987, which has been rounded to four decimal places. What does this mean? The values of the arguments tell us that we are working with the normal distribution that has a mean of 6 and a standard deviation of 12. We are trying to determine what percentage of the distribution occurs for x less than or equal to 9. Equivalently, we want the area under the curve of this particular normal distribution and to the left of the vertical line x 9. NORM.S.DIST vs NORM.DIST There are a couple of things to note in the above calculations. We see that the result for each of these calculations was identical. This is because 9 is 0.25 standard deviations above the mean of 6. We could have first converted x 9 into a z-score of 0.25, but the software does this for us. The other thing to note is that we really don’t need both of these formulas. NORM.S.DIST is a special case of NORM.DIST. If we let the mean equal 0 and the standard deviation equal 1, then the calculations for NORM.DIST match those of NORM.S.DIST. For example, NORM.DIST(2, 0, 1, 1) NORM.S.DIST(2, 1).