While it’s possible to look up probabilities for a normal distribution using the z-table, it’s actually much easier to calculate probabilities in Excel for a couple of reasons. First, there’s no looking at a table — the NORMDIST function does the hard work for you (what goes on behind the scenes is that Excel looks up the probabilities for you). Second, Excel does the intermediate calculations for you. Most calculation errors happen in an intermediate step (such as calculating the z-score to look up) rather than the actual z-score itself. Excel can handle three types of probability calculations: more than, less than, and in between. These instructions work for Excel 2007 and Excel 2010.
Sample Question: Suppose that a contaminant in samples from a city’s water supply has a mean of 500 ppm and a standard deviation of 100 ppm. What is the probability that bacteria in a randomly selected water sample will be: 1/ less than 600 ppm 2/More than 600 ppm 3/between 400 and 600 ppm?
Calculate Normal Distribution Probability in Excel of Less than 600 ppm
Step 1: Click an empty cell.
Step 2: Click the “Insert” tab.
Step 3: Click “Insert Formula”.
Step 4: Type “Normdist” into the search box and then click “Go.”
Step 6: Enter your data into the box. For this example, type “600″ in the X box, “500″ in the Mean box, “!00″ in the Standard Deviation box and “true” in the cumulative box..
Step 7: Click “OK.”. This returns 0.84134474 in the cell you clicked in Step 1, which is the probability of getting under 600 ppm.
Calculate Normal Distribution Probability in Excel of More than 600 ppm
Step 8: Subtract your answer from Step 7 from 1:
Calculate Normal Distribution Probability in Excel of Between 400 and 600 ppm
Step 9: Repeat Steps 1 through 8 for the second value, which for this example is 400. The probability is 0.15865526.
Step 10: Subtract the larger probability from the smaller probability. In this example:
0.84134474 – 0.15865526 = 0.68269