Jimmy from Huntsville wants to arrange people on a bell curve
Use a pivot table to figure out the average score.
Sort the pivot table so the scores are arranged low to high. These scores will be the X values.
In an extra cell, calculate the AVERAGE() and STDEV() of the scores.
Use formulas to copy the data from the pivot table to a new range
Calculate a Y value for each person with =NORM.DIST(A2,Mean,StDev,False)
Create an XY Scatter chart with smooth lines
In Excel 2010 or Earlier, use Rob Bovey's Chart Labeler add-in (Google it…)
In Excel 2013, Add Data Labels, From Cells, Specify the names
Micro-adjust the chart to change the scale and move the labels that overset each other
To download this workbook: https://www.mrexcel.com/download-center/2018/06/place-people-on-bell-curve.xlsx
To create a bell curve without the people, use my previous episode 1663 here:
Table of Contents
(0:00) Plot people on a bell curve in Excel
(0:18) Recap of Video 1663 for a bell curve without people plotted on it
(0:40) Using NORM.DIST function
(1:02) Using a pivot table to summarize data by person
(1:37) Calculate Mean & Standard Deviation
(2:20) Using NORM.DIST for each person
(2:44) Creating X-Y Scatter Chart from People
(3:01) Changing Scale of X-Axis
(3:33) Labeling each point with name of person
(4:25) Fixing where two name labels overlap
(5:22) Summarize and Recap
Use a pivot table to figure out the average score.
Sort the pivot table so the scores are arranged low to high. These scores will be the X values.
In an extra cell, calculate the AVERAGE() and STDEV() of the scores.
Use formulas to copy the data from the pivot table to a new range
Calculate a Y value for each person with =NORM.DIST(A2,Mean,StDev,False)
Create an XY Scatter chart with smooth lines
In Excel 2010 or Earlier, use Rob Bovey's Chart Labeler add-in (Google it…)
In Excel 2013, Add Data Labels, From Cells, Specify the names
Micro-adjust the chart to change the scale and move the labels that overset each other
To download this workbook: https://www.mrexcel.com/download-center/2018/06/place-people-on-bell-curve.xlsx
To create a bell curve without the people, use my previous episode 1663 here:
Table of Contents
(0:00) Plot people on a bell curve in Excel
(0:18) Recap of Video 1663 for a bell curve without people plotted on it
(0:40) Using NORM.DIST function
(1:02) Using a pivot table to summarize data by person
(1:37) Calculate Mean & Standard Deviation
(2:20) Using NORM.DIST for each person
(2:44) Creating X-Y Scatter Chart from People
(3:01) Changing Scale of X-Axis
(3:33) Labeling each point with name of person
(4:25) Fixing where two name labels overlap
(5:22) Summarize and Recap
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2217: Place People on a Bell Curve.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Today's question, from Jimmy in my seminar in Huntsville, Alabama. Jimmy has data, he wants to summarize this data and then plot the results on a bell curve.
Alright? Now, one of my most popular videos out on YouTube is this one: number 1663, Create a Bell Curve in Excel. And given a mean and a standard deviation, I figured out the low, which is 3 times the standard deviation less than the mean, and the high-- 3 times the standard deviation more than the mean-- where the gap is-- and a series of X values here, and to figure out the height, use this function: =NORM.DIST of the X value, the mean, and the standard deviation, comma false (=NORM.DIST(A10,$B$2,$B$3,FALSE)).
And if you think about it, this video is really just using a series of fake X values here in order to get a nice looking curve. And we're going to use the same concept here but instead of fake X values, we're actually going to have the people down here and then the height will be this exact same formula. Alright.
So, now, Jimmy wanted to create a pivot table.
So we'll Insert, PivotTable, put it here on this sheet, click OK. People down the left-hand side and then their Average Score. Alright, so it starts out with Sum of Score, I'll double-click there and change that to an average. Great.
Now, at the very bottom, I don't want a grand total-- right-click and Remove Grand Total-- and we want to arrange these People high to low and this is easy to do in a pivot table.
Data, A to Z-- excellent. Alright. Now, we're going to do the exact same thing that we did back in Podcast 1663, and that's calculate a mean and a standard deviation. So the mean is an average of these scores, and then equals standard deviation of those scores. Alright.
Now that I know that, I'm able to create my y-value.
Alright, so a couple things we're going to do here. First off, you can't create a pivot table-- a scatter chart-- from a pivot table.
So I'm going to copy all of this data over and I'm just going to do that with =D2. Notice I'm careful not to use the mouse or the arrow keys to point to those. And so we have our values here. These will become X values, the Y value is going to become =NORM.DIST, here's the x value, comma, for the mean, that number, I'll press F4 to lock that down; for the standard deviation it's this number, again, press F4 to lock that down, and cumulative FALSE. (=NORM.DIST(K2,$H$2,$H$3,FALSE)) And we'll double-click to copy that down.
Alright. And then, don't choose the labels, just choose the X Y and we will insert a scatter chart with lines-- you can either choose the one with curved lines or a little straight lines. Here, I'll go with curved lines like this. And we now have all of our People placed on a bell curve.
Alright. Now, some things-- some formatting type things-- we're going to do here: First off, double-click down here along the scale, and it looks like our lowest number is probably somewhere around 50-- so I'll set a min of 50-- and our largest number-- our largest number-- is 88-- so I'll set a max of 90.
Alright. And now, we have to label these points.
If you're in Excel 2013 or newer, this is easy to do; but if you're in an older version of Excel, you're going to have to go back and use Rob Bovey's Chart Labeler add-in in order to have these point labels come from some place that's not in the chart. Alright, so we start out here. We're going to add Data Labels, and it adds numbers and they look horrible. I'll come here and say that I want More Options, Label Options, and I want to get the Value From Cells-- Value From Cells.
Alright? So the Range of cells is right there, click OK. Very important to use Value from cells before I uncheck the Y value. It starts to look good. I'll get rid of this. Now, the whole key here-- because you have some people that are kind of overwriting each other-- is to try and make the chart as large as possible.
We don't need a heading up there. Why? Just delete that. And I still see, like, Kelly and Lou and Andy and Flo are almost in the same place; Jared and-- Alright. So now, this is going to be frustrating-- these ones that overlap. But when we click on a label, we selected all the labels, and then click on a label again, and we select just a single label. Alright? So now. very carefully. try and click on Andy, and just drag Andy up into the left. Looks like Jared and Ike are together, so now that I'm in single label selection mode, it's easier. And then Kelly and Lou, drag them up like that. Maybe there's a better place that's not over-running Lou, or even, like, here I can drag it on either side. Alright, so, what do we have? We have started with a bunch of data, created a pivot table, figured out the mean and standard deviation, which just allows us to figure out the height-- the Y position for each of those scores, and the height of those, hopefully, we'll get people into a nice parabola shaped bell curve, like that.
I Love this question from Jimmy, this question is not in this book, but it'll be in the next time I write this book. I'll have to add this-- it's a a cool request and a cool little trick.
Bell curves are very popular in Excel.
But check out my book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Alright, wrap-up from this episode: Jimmy from Huntsville, wants to arrange people on a bell curve. So we use a pivot table to figure out the average score, sort the pivot tables to the scores-- arranged high to low-- get rid of the grand total at the bottom-- these are essentially going to be the X values-- and then off to the side, calculate the average and standard deviation of those scores and use formulas to copy the data from the pivot table to a new range, because you can't have an XY chart that intersects with a pivot table.
Calculate a y-value for each person with =NORM.DIST of their x-value, the mean, the standard deviation, comma FALSE; create an XY scatter chart with smooth lines-- if you're an Excel 2010 or earlier, you're going to use Ron Bovey's Chart Labeler add-in. I'm going to have you google that because, in case Rob changes his URL, I don't want the wrong URL here. In Excel 2013, had Data Labels, From Cells, specify the names, and then some adjustments-- change the scale along the bottom, I change them in and Max and then move the labels that over-set each other.
To download the workbook from today's video, use the URL in the YouTube description. I want to thank Jimmy for this awesome question in Huntsville, and I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Today's question, from Jimmy in my seminar in Huntsville, Alabama. Jimmy has data, he wants to summarize this data and then plot the results on a bell curve.
Alright? Now, one of my most popular videos out on YouTube is this one: number 1663, Create a Bell Curve in Excel. And given a mean and a standard deviation, I figured out the low, which is 3 times the standard deviation less than the mean, and the high-- 3 times the standard deviation more than the mean-- where the gap is-- and a series of X values here, and to figure out the height, use this function: =NORM.DIST of the X value, the mean, and the standard deviation, comma false (=NORM.DIST(A10,$B$2,$B$3,FALSE)).
And if you think about it, this video is really just using a series of fake X values here in order to get a nice looking curve. And we're going to use the same concept here but instead of fake X values, we're actually going to have the people down here and then the height will be this exact same formula. Alright.
So, now, Jimmy wanted to create a pivot table.
So we'll Insert, PivotTable, put it here on this sheet, click OK. People down the left-hand side and then their Average Score. Alright, so it starts out with Sum of Score, I'll double-click there and change that to an average. Great.
Now, at the very bottom, I don't want a grand total-- right-click and Remove Grand Total-- and we want to arrange these People high to low and this is easy to do in a pivot table.
Data, A to Z-- excellent. Alright. Now, we're going to do the exact same thing that we did back in Podcast 1663, and that's calculate a mean and a standard deviation. So the mean is an average of these scores, and then equals standard deviation of those scores. Alright.
Now that I know that, I'm able to create my y-value.
Alright, so a couple things we're going to do here. First off, you can't create a pivot table-- a scatter chart-- from a pivot table.
So I'm going to copy all of this data over and I'm just going to do that with =D2. Notice I'm careful not to use the mouse or the arrow keys to point to those. And so we have our values here. These will become X values, the Y value is going to become =NORM.DIST, here's the x value, comma, for the mean, that number, I'll press F4 to lock that down; for the standard deviation it's this number, again, press F4 to lock that down, and cumulative FALSE. (=NORM.DIST(K2,$H$2,$H$3,FALSE)) And we'll double-click to copy that down.
Alright. And then, don't choose the labels, just choose the X Y and we will insert a scatter chart with lines-- you can either choose the one with curved lines or a little straight lines. Here, I'll go with curved lines like this. And we now have all of our People placed on a bell curve.
Alright. Now, some things-- some formatting type things-- we're going to do here: First off, double-click down here along the scale, and it looks like our lowest number is probably somewhere around 50-- so I'll set a min of 50-- and our largest number-- our largest number-- is 88-- so I'll set a max of 90.
Alright. And now, we have to label these points.
If you're in Excel 2013 or newer, this is easy to do; but if you're in an older version of Excel, you're going to have to go back and use Rob Bovey's Chart Labeler add-in in order to have these point labels come from some place that's not in the chart. Alright, so we start out here. We're going to add Data Labels, and it adds numbers and they look horrible. I'll come here and say that I want More Options, Label Options, and I want to get the Value From Cells-- Value From Cells.
Alright? So the Range of cells is right there, click OK. Very important to use Value from cells before I uncheck the Y value. It starts to look good. I'll get rid of this. Now, the whole key here-- because you have some people that are kind of overwriting each other-- is to try and make the chart as large as possible.
We don't need a heading up there. Why? Just delete that. And I still see, like, Kelly and Lou and Andy and Flo are almost in the same place; Jared and-- Alright. So now, this is going to be frustrating-- these ones that overlap. But when we click on a label, we selected all the labels, and then click on a label again, and we select just a single label. Alright? So now. very carefully. try and click on Andy, and just drag Andy up into the left. Looks like Jared and Ike are together, so now that I'm in single label selection mode, it's easier. And then Kelly and Lou, drag them up like that. Maybe there's a better place that's not over-running Lou, or even, like, here I can drag it on either side. Alright, so, what do we have? We have started with a bunch of data, created a pivot table, figured out the mean and standard deviation, which just allows us to figure out the height-- the Y position for each of those scores, and the height of those, hopefully, we'll get people into a nice parabola shaped bell curve, like that.
I Love this question from Jimmy, this question is not in this book, but it'll be in the next time I write this book. I'll have to add this-- it's a a cool request and a cool little trick.
Bell curves are very popular in Excel.
But check out my book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Alright, wrap-up from this episode: Jimmy from Huntsville, wants to arrange people on a bell curve. So we use a pivot table to figure out the average score, sort the pivot tables to the scores-- arranged high to low-- get rid of the grand total at the bottom-- these are essentially going to be the X values-- and then off to the side, calculate the average and standard deviation of those scores and use formulas to copy the data from the pivot table to a new range, because you can't have an XY chart that intersects with a pivot table.
Calculate a y-value for each person with =NORM.DIST of their x-value, the mean, the standard deviation, comma FALSE; create an XY scatter chart with smooth lines-- if you're an Excel 2010 or earlier, you're going to use Ron Bovey's Chart Labeler add-in. I'm going to have you google that because, in case Rob changes his URL, I don't want the wrong URL here. In Excel 2013, had Data Labels, From Cells, specify the names, and then some adjustments-- change the scale along the bottom, I change them in and Max and then move the labels that over-set each other.
To download the workbook from today's video, use the URL in the YouTube description. I want to thank Jimmy for this awesome question in Huntsville, and I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.
