Visualizing Data with the Normal Distribution Curve in Excel

The normal curve is a visual representation of the bell curve. That is something we have yet to create and we're actually going to create now in a chart form. We'll use the normal curve to better visualize the data that is normally distributed about the mean.

So that is the standard deviations that fall between one, two, or three standard deviations around the mean. We're going to build a chart from scratch using some scores. Again the normal curve is the bell shaped curve that is symmetric around the mean and in that particular normal distribution the mean, the median, and the mode are all the same.

So our first practice is to create that curve using some scores. Now I know it's unusual that we're skipping this section with C scores and the normal distribution function but we'll come back to it. What we're going to work with are some students here.

Let's see how many students we have. We have 60 students and these are their scores. Now we want to be able to figure out what the normal distribution is.

Before we can figure that out we have to calculate the mean and the standard deviation. This is something you learned earlier from the measures of central tendency and also from measures of dispersion. So let's calculate the mean.

That's going to be equal to the average of all the scores we have here on the left. I'll press ENTER. I get 88.2. Now if I decrease the decimal to round this it's actually 83.

Now I want to calculate the standard deviation. The standard deviation can easily be calculated by selecting all the values that I selected for average. I'm using standard deviation P because I'm assuming this is the entire population and not just a representative sample.

Control shift down. I'll press F4. It takes me right back to the calculation.

I'll press ENTER. Now while I'm doing this I'm also getting some information about the scores using normal distribution. What we're going to learn about normal distribution we haven't learned about that just yet but that's what got calculated from simply coming up with the mean and standard deviation.

The chart you see here on the right is missing the y values. Right now they're blank so that's what I'm going to fill in for the grades. In order to do this I need to use the normal distribution function.

The normal distribution function requires both the mean and standard deviation which I just calculated. The normal distribution is norm dot dist. The first value that I'm going to select is X and they let me know very clearly that's the first value I need.

X in this situation is the first score. The score directly to the left of the function. I'm going to enter a comma and then I need to select the mean.

There's the mean. There's something I have to do which is very important. I need to lock the position of the mean because when I drag this down I don't want the mean to shift over into standard deviation and start selecting blank cells.

I'm going to press F4 to lock the position of the mean. I'll enter a comma and then I'll select the standard deviation. I also need to lock that.

I'll press F4 then I'll enter a comma and I have two choices either true or false. True stands for cumulative distribution function. It allows you to get values that are cumulative and span across a range.

Since I'm interested in creating plot points I want to focus on an exact value. So this is very similar to a VLOOKUP where you're looking for an exact match. So what you usually choose for that is false or zero.

Here I'm going to choose false. I'll press ENTER and I get my first point for 95. Now I'm going to fill in all the values by simply selecting that cell and double clicking on the black plus sign.

I have now created my bell curve. 83 is the mean and as you can see it's approximately right there between 80 and 85. You can also tell 83 is the mean because we've highlighted that here.

When you get to 83 that's the closest to zero that you're ever going to get. Everything else falls within a certain standard deviation from that value. So that's actually what we want to figure out next.

What is the standard deviation? Well to figure that out we could use something called a z-score. A z-score will tell us what the standard deviation is for any particular type of value. Now I have a chart here that represents values where the mean is 4.00. The z-score is a calculation that's derived from the value minus the mean divided by the standard deviation and that will tell you how many standard deviations you are from the mean.

Here's the calculation which is the mathematical version. The value in this case is 11.20. The mean is 4. The standard deviation is 7.1. When I press ENTER this is telling me that 11.20 is one standard deviation away from the mean. I could enter in some other values.

How about negative 0.32? Oops I entered in something incorrectly. Forgive me. Let me type negative 3.2. That is negative one standard deviation away from the mean and that would make sense based on what the mean is.

Well let's try 7.6. That is half a standard deviation away from the mean. How about 18.4? That is two standard deviations away from the mean. If you think about it 7.2 and 7.2 is 14.

That goes past the threshold of one standard deviation and takes you to two standard deviations. All right so I'll change this back to 11.2 and that is one standard deviation away. Now what if you didn't want to do the math? The good news is that there is a standardize function that will calculate the standard deviation.

So it's equal to standardize. It tells you clearly what you need to select. X which in this case is 11.20 comma I need the mean which is 4 comma and then I need the standard deviation which is 7.21. When I press ENTER I get the same result that I got when I use the calculation.

Now that we know how to calculate the standard deviation using the z-score I'm going to go back to our original table down here. I have all of these values. I want to calculate the standard deviation.

What is the standard deviation for all of these scores? Well I'll go here to standardize. I'll type equal. Now this is not going to be part of the chart.

This is just going to show me the standard deviation. I'll type standardize. As soon as you type standardize it tells you what you need to select.

X which is the grade comma the mean which you'll need to lock comma and the standard deviation which you'll also need to lock. I'll press ENTER and we are at two standard deviations from the mean if the grade is 95. So that's right here.

I'm going to double click on this and we get all the other standard deviations. So there's one, there's two, here's one and here we just get past one and go to two. So that's how you can calculate the standard deviation.

You may have a question about the normal distribution. We use normal distribution to find a specific plot point and a plot point as it relates to this graph which I pulled away from. Let me undo this which I pulled away from this spot right here.

So the probability mass function is focused on a specific point on the bell curve whereas the cumulative version of that measures values from that starting point down to zero or all the way to the left. So I want to use the true version of the normal distribution function to calculate a percentage of values that fall below zero. If everything above zero is considered profit I want to measure our loss.

So I'm going to go over here and I want to find out the percentage of our loss. So I'm going to use the normal distribution function. So let me just change this font color to here to black.

Do that here too just in case. Equal NORM X in this case is zero. That's our starting point.

I'll enter the comma then I'll select the mean. I'll enter the comma and then I'll select the standard deviation. This time instead of false I'm going to choose true for the cumulative distribution function.

When I press ENTER I get 29 percent. This is basically saying the value from zero all the way down to negative 10 is 29 percent or about 30 percent of the bell curve and that appears to be so here. If I wanted to calculate my profit my profit will be equal to one which is a standard for a stand-in for 100 percent minus my loss which is 29 percent.

Zero and everything underneath that. So that is going to be 71 percent. This can be used to also calculate a range.

So I want to calculate the percentage from five to ten for whatever reason. This is looking like it potentially could be around 25 percent. We'll see.

So I'll go over here. Now for this there isn't a specific function where you could subtract each of those points five and ten. So what you have to do is select all the values from ten and below and all the values from five and below and then you'll subtract them and the difference will be the difference between five and ten because anything before five cancels out anything before five when you go from 10 down to negative 10.

So I'll go here. I'll type norm dot distribution. I'm going to select the value which in this case is going to be a value that I put in and that is going to be 10.

So it's not already in the spreadsheet. I have to type it in. I'll enter a comma.

Now I'll select the mean. I'll enter a comma and then I'll select the standard deviation. Then I need to choose whether or not this is a point or a cumulative distribution.

It's going to be cumulative distribution. I want to grab all the values from 10 and below. So I'll type true, close parentheses.

Then I want to subtract normal distribution and this time I'm going to enter in the value for five. Why? Because if you're subtracting something you should start with the higher number and then subtract the lower number. So it's 10 minus five and then I just go back again and choose the mean, enter a comma, choose the standard deviation, enter a comma and I also want to use true for a cumulative distribution function.

Close parentheses and now I'm about to find out the percentage between five and 10. And there it is. I'll change this to a percentage.

I have the raw value. It's 24.22 percent, which like I said looks like about 25 percent of the values for this range. Because when you start to get to zero, it's like negative five to zero, zero to five, five to 10 and then 10 to 15.

It's just like about four grids right here. Although there's a little bit after five here, not much, a negative five. So this section we cover a lot of things.

We created our own curve right here by using the normal distribution function to calculate the y value. Then we calculated the standard deviation using the z score and the standardized function in excel. I showed you how we could use the normal distribution to grab multiple values from a starting point.

In this case, our starting point was zero. It was also five and 10. If you wanted to subtract values, then you would start with the higher value, use true for the cumulative distribution function and then also do the same for the next value.

All right, so that is normal curve. Thank you for watching.