Several comments on episode 1663 asked how to add a mean to the bell curve chart. This podcast shows how to create a 2x2 range to represent a single XY line and how to Paste Special that series to the chart. To watch the original video that shows how to set up the formulas for the bell curve, watch episode 1663:
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Podcast Episode 1825: Bell Curve - Add A Line To Show The Mean.
Hey. Welcome back to the MrExcel netcast.
I'm Bill Jelen. Well, going back to episode 1663 where I showed how to create a bell curve in Excel, got a couple of comments in the last week: how would I show a line on the curve to see the mean? So, this podcast isn't going to show you how to create the bell curve.
You’ll have to go back to episode 1663 to see that. Must be that time in the Statistics class in college where they are getting to bell curves.
So, we have our data here. I'm going to select that data and I'm going to create a line chart.
You could create an XY chart but here I'm going to create a line chart -- no points, just a simple little line chart there. Get rid of the title. Get rid of the legend. Of course, if you’re in Excel 2013, the legend won't be there at all, and what we want to do is we want to show a line going from the mean all the way down.
Alright, so, to do this, we're going start with a two-row by two-column range and the first row is going to be the first point.
The first point is supposed to show up at the mean, which is 47 at this point, with a height of 0 and the second point will be at the mean with a height of whatever the correct result is. So, the x value is going to be 47 with a height of 0 and then 47 with a height of whatever the value for 47 is down here, and maybe instead of typing that 47, you could have just pointed to the mean. Either way, somehow, we just need to get these four cells arranged like that in Excel. I'm going to select those cells, CONTROL+C, and now the next several steps, it is going to look like this did not work at all, but that's okay. We're going to make it work.
Click on the chart, open the PASTE drop-down, and choose PASTE SPECIAL. This is a great version of PASTE SPECIAL when we have a chart selected. We're adding the cells as a NEW SERIES and the CATEGORIES (X LABELS) are in the first column. Click OKAY. Now, initially, we just get this line here which doesn't look very good. Select that line, come out to the CHART TOOLS, DESIGN -- in Excel 2010, on the left-hand side, CHANGE CHART TYPE; in Excel 2013, it's on the right-hand side. So, I’ll change the chart type and we will change this to an X Y SCATTER with straight lines like that. Do we want points?
If you do, great. If you don't, select there.
Click OKAY.
Alright. So, now, there's our line but it's certainly not appearing at the mean and what's going on? Well, the first axis for the line chart is going from 26 out to 68. The second axis for the XY chart, they just randomly chose 0 to 50. So, let's choose that. I'll double click and we're going to change this to be FIXED to match the bottom axis, so 26 and then 68, good enough, click CLOSE, alright, and we now have our red line appearing there at the mean. We don't need to show this top axis. We can actually click up there and do CONTROL+1, a little bit of formatting, say that we don't need to see any tick marks, AXIS LABELS, NONE. Alright. That will get rid of that. Also the axis on the right-hand side, same thing: CONTROL+1, tick marks, NONE, AXIS LABELS, NONE make that go away.
So, there you go -- the bell curve which we talked about in episode 1663 and then today using these four cells to add a brand new XY series that will show the mean.
[ Alright – 03:44 ]. Hey. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Podcast Episode 1825: Bell Curve - Add A Line To Show The Mean.
Hey. Welcome back to the MrExcel netcast.
I'm Bill Jelen. Well, going back to episode 1663 where I showed how to create a bell curve in Excel, got a couple of comments in the last week: how would I show a line on the curve to see the mean? So, this podcast isn't going to show you how to create the bell curve.
You’ll have to go back to episode 1663 to see that. Must be that time in the Statistics class in college where they are getting to bell curves.
So, we have our data here. I'm going to select that data and I'm going to create a line chart.
You could create an XY chart but here I'm going to create a line chart -- no points, just a simple little line chart there. Get rid of the title. Get rid of the legend. Of course, if you’re in Excel 2013, the legend won't be there at all, and what we want to do is we want to show a line going from the mean all the way down.
Alright, so, to do this, we're going start with a two-row by two-column range and the first row is going to be the first point.
The first point is supposed to show up at the mean, which is 47 at this point, with a height of 0 and the second point will be at the mean with a height of whatever the correct result is. So, the x value is going to be 47 with a height of 0 and then 47 with a height of whatever the value for 47 is down here, and maybe instead of typing that 47, you could have just pointed to the mean. Either way, somehow, we just need to get these four cells arranged like that in Excel. I'm going to select those cells, CONTROL+C, and now the next several steps, it is going to look like this did not work at all, but that's okay. We're going to make it work.
Click on the chart, open the PASTE drop-down, and choose PASTE SPECIAL. This is a great version of PASTE SPECIAL when we have a chart selected. We're adding the cells as a NEW SERIES and the CATEGORIES (X LABELS) are in the first column. Click OKAY. Now, initially, we just get this line here which doesn't look very good. Select that line, come out to the CHART TOOLS, DESIGN -- in Excel 2010, on the left-hand side, CHANGE CHART TYPE; in Excel 2013, it's on the right-hand side. So, I’ll change the chart type and we will change this to an X Y SCATTER with straight lines like that. Do we want points?
If you do, great. If you don't, select there.
Click OKAY.
Alright. So, now, there's our line but it's certainly not appearing at the mean and what's going on? Well, the first axis for the line chart is going from 26 out to 68. The second axis for the XY chart, they just randomly chose 0 to 50. So, let's choose that. I'll double click and we're going to change this to be FIXED to match the bottom axis, so 26 and then 68, good enough, click CLOSE, alright, and we now have our red line appearing there at the mean. We don't need to show this top axis. We can actually click up there and do CONTROL+1, a little bit of formatting, say that we don't need to see any tick marks, AXIS LABELS, NONE. Alright. That will get rid of that. Also the axis on the right-hand side, same thing: CONTROL+1, tick marks, NONE, AXIS LABELS, NONE make that go away.
So, there you go -- the bell curve which we talked about in episode 1663 and then today using these four cells to add a brand new XY series that will show the mean.
[ Alright – 03:44 ]. Hey. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
