 Statistics and Excel. Combining two histograms on one chart, part number three. Get ready, taking a deep breath, holding it in for 10 seconds and looking forward to a smooth soothing Excel. First, a word from our sponsor. Yeah, actually we're sponsoring ourselves on this one because apparently the merchandisers, they don't want to be seen with us. But that's okay, whatever, because our merchandise is better than their stupid stuff anyways. Like our, trust me, I'm an accountant product line. Yeah, it's paramount that you let people know that you're an accountant. Because apparently we're among the only ones equipped with the number crunching skills to answer society's current deep complex and nuanced questions. In a prior presentation, with just our data set, if you do have access to this workbook, three tabs down below. Example, practice, blank, example, in essence, answer key, practice tab, having pre-formatted cells so you could get right to the heart of the practice problem. Blank tab is where we started with just our data set so that we can practice formatting cells within Excel as we work through the practice problem. So what did we do last time? We had our data set on the left related to heights. We created a table with it within made a histogram from it. Now because we're dealing with heights and we have a pretty extensive sample of heights, we get this nice shape which looks kind of like a bell type shape where we have most of the results somewhere in the middle and they're tapering off to either side. Then our objective is to take two data sets and put them on the same chart. To do that, it's not so easy to do if I go to the insert tab with just a histogram, easier with say a bar chart. So what we did is mirror our histogram with a bar chart by creating our buckets manually, not really manually, but using Excel to do it instead of using a histogram to do it. And then we created a bar chart. So now we have something that we might be able to plot some other data on it as well. So we then said let's take our data set and make another related data set. Now to do that we just took the prior data set times 0.95 imagining this was for men, this was for women. That's not actually the case with this data set but it just gives us an idea of what we can now do. So we then made a histogram from that data set and it looks similar to this histogram but the middle point is different because of course we just shifted it by multiplying by 0.95 on the second data set. We then created a bar chart in a similar way as we did over here but we wanted to make the buckets line up to the same buckets. This is where it gets kind of tricky because these bucket size, if you want to put two of these bar charts on the same graph, you want to have the same bucket sizes. So we took these bucket sizes over here and extended them to the lower regions so that we have enough buckets to contain all the data sets for the men and the women. Alright so now we're going to put them on the same graph. Now before we do that let's imagine that we just put them on top of each other. Like if we added these two data sets together in one data set and we made one histogram from it, it's going to come up with a mush, just a bunch of, it's not going to separate the two data so let me show you what I mean. So let's copy this entire data set right here for the heights. I'm going to put my cursor up top. Let's just select the entire column of Z and then I'm going to right click and copy. I'll bring that on over to the right and let's just test that out in AY. We'll right click and I'm just going to paste it. Did I paste it 1, 2, 3? Let's undo that. Let's just paste it normal. Paste it normal. I did paste it normal. Okay so it pulled in and then I'm going to go back on over and I'm also going to take the data set for the women. Now I'm going to copy just the women's data by putting my cursor in the dropdown and copying just the data. So now I'm not getting the header and right click and copy and then I want to put this underneath so we have this very large data set. So I'm going to go into the data set. This goes down quite far so to get to the bottom I'm going to hit CTRL SHIFT and down. That takes me to the bottom and then I'm just going to paste the data right here at the bottom. I'm way down here 25,000 so I'm going to right click and paste this data. Let's paste this 1, 2, 3. And so now we have all this data going all the way down to 5,000 lines. We just put the two data sets on top of each other. What would that do if we entered the histogram to it? Let's put a table around it just to lock this in place. So I'm going to go to the Insert Tab, Table Group and insert a table. And so it's going all the way down to 5,000. That looks right. The dancing ants are doing their mamba around the proper location. I don't care if they're dancing as long as they're doing the work. You work hard, you play hard. If the ants want to dance then they can as long as they get it done. As long as you get it done. That's what I'm talking about anyway. So then I'm going to go over here and I'm going to insert. Let's put our cursor up top and so now I'm going to select all that data and let's just make a histogram from that. So I'm going to go to the Insert Charts and then drop down and let's make a histogram. So there it is. So now you'll notice what happened here is it kind of mushed the two histograms together. So when you do that you might expect that it's going to give you kind of two histograms or something. But no, it's going to mush all the data together so I can't really separate the two data sets so we get this wider histogram as opposed to... So you can kind of imagine what happened here. We had these two histograms that we had. And this one we took the two data sets and put them together and we get something that looks like this. And you can imagine these are really kind of two histograms that are kind of mushed together because if we break them out in two data sets, let's do that now. So now what I want to do is say well what I'd like to do is show two different data sets maybe have them as a different color. So what I want to do is take my buckets, take all this stuff that we made for the men and the women. And I'll put them together and then put them on the same graph. So let's put them together like that. I'm going to go to the left and let's choose this whole data set. All I really need are the buckets that we made and then the results. So I'm going to copy that. So I'm going to take this all the way down and right click and copy. And I'm going to go to the right to where we want to put it. And so we'll put it all the way over here somewhere in like BP. And I'm going to right click and I'm going to paste it just one, two, three. I don't really need the formulas so I'll just paste it one, two, three. So there's just our data that we made. And it might even be safer by the way to do this with a formula. So instead of doing it that way, let's show you another way we can do it. I'm going to undo that. I could say I'm going to start here in BP and say equals and then I'm going to point to that data set. So then if I changed my data set, this graph would change with it. So I'm going to point to this number in E17. Notice when I scroll over, you can still see where the formula bar up top. And if I hit enter, it'll bounce me back over here. And then I can copy this to the right. So this is actually a reference in E17. Copy that to the right and then I'm going to copy these two cells down. I'm just going to go until I don't have any more data. So I'll just copy it down and it's still going like that bunny with the battery in it. We'll keep on rolling till the battery runs dry and the bunny falls over and puts his head. His head goes right through the drum that he was banging because he ran out of power. So let's go up top and then make this a little bit larger. Okay, so that's our first data set. So now if I was to select that data and insert and let's put a header on it. I'm going to select these two. I'm going to put something above it. So I'm going to insert cells above it. So I'm going to select those two, right click, insert and then shift the cells down. So I'm going to call this the men's data set and we'll make that our headers, home tab, fonts group. Let's make it black and white. And maybe I should put the men over here because I'll try to use the same columns too. And then I could make a bar chart out of that, our bar chart histogram that we made before. I can take this data and say I want to scroll up and insert. Then the not the histogram, but the bar chart, which will make in essence a histogram like thing. So there we have it. So we did that in the prior presentation. Now we want to take the data set, the second data set for the women and put them on this same chart. So make this nice and wide. And so now we want to take the second data set. So let's start by doing the same thing. I'm going to make a skinny BR. Let's make a skinny BR and I'll pull this to the right. And then I'm going to put my cursor in BS and say equals. And I'm going to pull in the data set from the women. So I'm going to go on over here. The buckets that we put together for the women in a prior presentation are down here. So here's our bucket sizes. So we'll take these buckets 57 to and then to 57 52 enter. I'm going to copy that to the right. Copying it to the right. Roger that copy out Roger. That's airplane speak case you don't know as a pilot. So then I'm going to copy this down and we're going to copy this down. And so, so there we have those. So those have been copied down. Now I want to line up these two buckets. So notice this one is starting at 57. So for the men, I'd like to kind of push this down until it until it gets to the 60. So this line in other words, I want to line up that line with this line 60 60 28 this one. So how can I do that? Well, I could select the whole thing and pull it down. I could insert cells. I can cut and paste. Let's try inserting cells. So I'm going to select from here to here and then I'm going to insert cells above it, which should pull these down. So this is the way that I think is most unintuitive to most people, but highly useful. So I'm going to right click and insert and then I want to shift the cells down. So I'm going to say shift the cells down. Now notice that it formatted it like the cell above it on these blank ones. If you hit just undo, then it usually undoes just the formatting or sometimes it does. I'm going to redo it. It didn't do it that time. And sometimes it also has that little formula bar, but you can also just I'm going to put my cursor on some other cell that doesn't have any formatting in it. Home tab, clipboard, paintbrush, and then I'm going to paintbrush this formula. Okay. So, so, so there we have it now. Let's add the extra, the extra buckets here. I'm going to put them on the men side adding the extra buckets. So there's our buckets where the men have zero. I'm going to put zeros in the men's data for those buckets. So now I'm going to be using this, this as my X because it has the buckets which are going to be useful for both the men's and women's data. And then I can pick up these two data sets men and women here. So we'll make this black and white. So now we need to adjust our data. So if I click in the data, you'll see that we're not including this stuff over here. So the easiest way to do that is we can, we can click on it and see the, and just drag these up. So now we're picking up the data and the columns. Now, if the other way you can do that is you can go to the chart designs and select data. And you'll see here's the data series. So if I go into that one and go into my data, it's picking up this column. And then on this side, these are the buckets, right? So you can see it's picking up the buckets. Okay, so now we want to add what we want to do is add now the woman's data, which is properly lining up to the same buckets. So all we need to do is add another data series. I don't need to add the buckets because the whole trick here is that the buckets are lining up for both of them. We have the same X side. So now we can line up our two Ys in our chart. So the way to do that is we can go into our chart again, chart design, and I want to say select data. And now I want to have another series of data on the left, which is just going to be the results because the buckets are the same. So I'm going to then say, let's hit the add data. And then we'll call the series name. I'll just say W and then we pick up the data. And again, we just need the data size. We don't need the buckets. So I'm just going to select the data here in BT, BT two and control shift down. That's the entire data. So I'm going to say, okay. And so now we've got these two data series and I'm going to say, okay, let's see what that does. And now we've got these two that have been populated on the same graph. So that which makes kind of sense. So now we've got this one, right? We can see the middle point here is at is at the 64 about, which you'll recall if we go over to our histogram for the women's data around 64. The histogram for the men or the whole thing will imagine men 67. So the second middle point is, you know, around that 67. Right. So obviously this one is very, very similar looking because we just took, we just basically took the two, two data sets and multiplied one data set times point nine five. But you could see, you know, how they would be, how you could basically plot two line items on the same graph. Now, once we have these, you might then want some labels on something like this. So we might then go, okay, let's see if I can say the plus button. And we might want, we might want a legend for this one. So we could add a legend. So we've got series one and series two. And then within the legend, we could rename those datas. So if I go into the chart design and I say, I say select data, then I can rename the series names up here by editing series one, which I might want to edit and just call it men. And then okay, and then series two, edit and call the name women. Or you might take it actually from here as well and say, okay, that'd probably be a better way to do it. So, so there we have that. And then you could do the same formatting with your datas and make them a little bit wider if you wanted to. So you could go into your, your data and we can go into the width and see if I can change it to 0.25. And so now it's changing one line at a time. So sometimes it gets a little finicky controls the control to to change the data of all of the data set when you have multiple items on it. So there I was able to adjust it a little to make it a little bit wider over there. So you can kind of tinker with that. But you can see here that they have them spaced out so you can see the two on the overlap. Now you could select the datas and change the fill color of the data so that it's, you know, a lighter data set if you so choose. Or you can have the no fill solid fill and adjust that the gradient fill. So you might choose, you know, different fill patterns, which could help with basically the overlap. So here's different fill patterns that you can have that might might help to emphasize one of the data sets versus the other data set. So you can play with those. And automatic. So those are the those are the salt stop it here so we don't run too long but you have the normal kind of formatting tools. It's just when you have to the two data sets on one graph, then you might do some of the tools to kind of highlight one data set versus the other data set. You might play more with the colors of the data set on them. So let's go back on over here and just do our normal formatting. Select the entire data set and let's just make it blue and bold some or blue and bordered home tab font group border and then blue. And then let's select this data set and do the same thing. I'll just say font blue and border. And so that looks good. And then over here, we just took that whole data set so that looks good. So that's the that's the general idea.