An addendum to episode 1825 - several easier ways to add a line at the Mean of a bell curve: Drawing object, Error Bar, High-Low Line, Dropline.
Transcript of the video:
Learn Excel from MrExcel, Podcast Episode 1825b: Bell Curve - Line At Mean 5 Ways.
Hey.
This is really an addendum to yesterday's episode 1825 and it goes back to episode 1663 where I showed how to create a bell curve.
Someone had said, how do we draw a line at the mean, and [ in yesterday, episode 1825, XY chart with two points, that's not the easiest way to do this.
The easiest way to do this is to come here to the drawing tools, INSERT, SHAPES, just grab a line, and then hover right there, POINT 47, hold down the SHIFT key and drag straight down to the axis, and you have a line.
There's other ways to do this.
Here we have our first series.
I'm going to add a second series and, at the mean, the second series is going to have a point there.
So, we click on the chart to get the blue outlines, drag the blue outline over to add the second series, alright, and then we want to select that point, so, FORMAT, SERIES “LINE”. We’re just selecting that single point right there, and then on the LAYOUT tab, we're going to add ERROR BARS, MORE ERROR BAR OPTIONS, we want to go MINUS only, and a FIXED VALUE of whatever the mean height happens to be – so, .056992 – MINUS, click CLOSE, and that will draw an error bar at the mean.
Another way to go: same thing.
Add the second series but this time we're going to make the height be 0 for that point, so now we have two lines.
Basically we have the line that’s the curve and then the line at the mean, and then, select the chart, LAYOUT tab, LINES, SHOW HIGH-LOW LINES, and that draws a line from the highest point to the lowest point.
Because there are no points here, we get no lines.
I really wanted to do this with a drop line but I can't get it to work.
It’s very frustrating.
So, if I come here to LAYOUT and select SERIES “LINE” which is that single point and then say that I want to add drop lines, they're adding drop lines to series 1 and series 2.
That must just be something with the chart.
So, to do this with a drop line -- and this is horrendous as the original idea of the XY chart -- I'm going to click on the chart and CONTROL drag to create a copy of the chart.
This version over here is going to be pointing to my height which is zero in most cases but equal to the mean in the second case.
So, we have that chart now.
We're going to FORMAT, FORMAT SELECTION, LINE COLOR, NO LINE, click CLOSE.
Okay.
So, now we just have the points.
Now we can come to LAYOUT, LINES, DROP LINES, get that single line there.
Two more things we have to do though before we can line these back up.
So, we choose the CHART AREA, FORMAT, SHAPE FILL, NO FILL, and then PLOT AREA, FORMAT, SHAPE FILL, NO FILL.
That makes this chart be transparent so that way we can see through the chart.
Alright, so, I’m going to CONTROL click on the chart to select the chart container and we'll just use the UP ARROW key and the LEFT ARROW key to get those lined up perfectly.
You could do this with a mouse but it's a lot easier to do it with the arrow keys because you have pixel by pixel control.
Alright, there.
Okay, so, now we have, in essence, a drop line but only from that one point.
Probably would be nice if they would let drop lines work from just one series and not the other series but here we are.
Okay, so, just a quick addendum to yesterday.
Lots of different ways to do that line at the mean, certainly using the insert drawing tools, although it seems to be cheating if you're doing this for your college class.
They'll probably think that it's just like, you know, grabbing a pencil and drawing a line in, but lots of different ways to go.
Alright.
I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
Hey.
This is really an addendum to yesterday's episode 1825 and it goes back to episode 1663 where I showed how to create a bell curve.
Someone had said, how do we draw a line at the mean, and [ in yesterday, episode 1825, XY chart with two points, that's not the easiest way to do this.
The easiest way to do this is to come here to the drawing tools, INSERT, SHAPES, just grab a line, and then hover right there, POINT 47, hold down the SHIFT key and drag straight down to the axis, and you have a line.
There's other ways to do this.
Here we have our first series.
I'm going to add a second series and, at the mean, the second series is going to have a point there.
So, we click on the chart to get the blue outlines, drag the blue outline over to add the second series, alright, and then we want to select that point, so, FORMAT, SERIES “LINE”. We’re just selecting that single point right there, and then on the LAYOUT tab, we're going to add ERROR BARS, MORE ERROR BAR OPTIONS, we want to go MINUS only, and a FIXED VALUE of whatever the mean height happens to be – so, .056992 – MINUS, click CLOSE, and that will draw an error bar at the mean.
Another way to go: same thing.
Add the second series but this time we're going to make the height be 0 for that point, so now we have two lines.
Basically we have the line that’s the curve and then the line at the mean, and then, select the chart, LAYOUT tab, LINES, SHOW HIGH-LOW LINES, and that draws a line from the highest point to the lowest point.
Because there are no points here, we get no lines.
I really wanted to do this with a drop line but I can't get it to work.
It’s very frustrating.
So, if I come here to LAYOUT and select SERIES “LINE” which is that single point and then say that I want to add drop lines, they're adding drop lines to series 1 and series 2.
That must just be something with the chart.
So, to do this with a drop line -- and this is horrendous as the original idea of the XY chart -- I'm going to click on the chart and CONTROL drag to create a copy of the chart.
This version over here is going to be pointing to my height which is zero in most cases but equal to the mean in the second case.
So, we have that chart now.
We're going to FORMAT, FORMAT SELECTION, LINE COLOR, NO LINE, click CLOSE.
Okay.
So, now we just have the points.
Now we can come to LAYOUT, LINES, DROP LINES, get that single line there.
Two more things we have to do though before we can line these back up.
So, we choose the CHART AREA, FORMAT, SHAPE FILL, NO FILL, and then PLOT AREA, FORMAT, SHAPE FILL, NO FILL.
That makes this chart be transparent so that way we can see through the chart.
Alright, so, I’m going to CONTROL click on the chart to select the chart container and we'll just use the UP ARROW key and the LEFT ARROW key to get those lined up perfectly.
You could do this with a mouse but it's a lot easier to do it with the arrow keys because you have pixel by pixel control.
Alright, there.
Okay, so, now we have, in essence, a drop line but only from that one point.
Probably would be nice if they would let drop lines work from just one series and not the other series but here we are.
Okay, so, just a quick addendum to yesterday.
Lots of different ways to do that line at the mean, certainly using the insert drawing tools, although it seems to be cheating if you're doing this for your college class.
They'll probably think that it's just like, you know, grabbing a pencil and drawing a line in, but lots of different ways to go.
Alright.
I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
