 Statistics and Excel. Combining two histograms on one chart. Got data? Let's get stuck into it. With statistics and Excel. Actually, we're using OneNote here, but we'll still be talking about Excel. You're not required to, but if you have access to OneNote, we're in the icon left-hand side. OneNote Presentations 1345. Combining two. 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. If you would like a commercial free experience, consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com. Two histograms on one chart tab. We're also uploading transcripts to OneNote so you can use the immersive reader tool. Changing the language if you would like being able to either read or listen to the transcripts in multiple languages using the time stamps to tie in to the video presentations. OneNote desktop version here. Data on the left hand side. Same data set that we've seen in prior presentations related to the height of individuals measured in the units of inches. So in other words, we have individuals of a population which we are imagining that we measured their heights and came up with the first individual 68.5 inches. Second individual 66.79 inches. Third individual 69.9 inches and so on and so forth. Now this is a pretty extensive data set. We didn't add the whole data set here because it's kind of too long to really want to add in a OneNote presentation. So if you work this in Excel, it'll be a longer set of data that we then used to create the histogram. Now because we're dealing with something in nature such as measurements of things like height, the histogram has this familiar type of shape where you would expect most of the heights of the population to be hovering somewhere in the middle and it to be tapering out in somewhat of a uniform kind of patterns. That's what we have here, kind of a bill type of shape. Now what we want to do with this is to be thinking about, well, what if we had another set of data, would we be able to map it on top and have two of these basically histograms on one chart? Now it's a little bit more difficult to do than you would think at first. In Excel, it's difficult to do with simply the histogram, but if you make the histogram from the bar chart, then you're more likely to be able to do that. Now we're doing that with a pretty extensive histogram here because this is a long list of data, so if you have other types of histograms, it might be a little bit easier. However, the first step to do this would be to say, let's make this same histogram, but do it in the form of a bar chart. So you can see what I can do then is list out my buckets and we have a lot of buckets here from 60.28 to 60.51, 60.51 to 60.74, and so on and so forth until we get to this last bucket outside. What that is doing is taking all of the results and this long set of data and counting how many of them fall into each of these buckets. We can do that manually in Excel by simply saying, I'm going to create my lower end buckets of 60.28 and then my upper end buckets which are starting at the 60.51 and the difference between those two buckets is 60.51 minus 60.28 is 20.23. And so we have a bunch of buckets which are .23 difference between them. And so then in Excel I can just say, I want to add the one above it plus .23 and I can easily make these two columns even though there's a lot of them by just dragging down the formula. So we'll do that in Excel if you want to see how that's done in Excel. And then we can then create our bucket range which mirrors what we want on the x-axis. So I want this on the x-axis. Now I could simply type this in but it would be easier if I could use a formula to put this in play. And I have the bottom, I have the top, I want to show it in one cell. So you can do that in Excel by using this formula. It looks a little complicated at first but it's not too bad. It equals C17 represents this cell and because we're going to connect it to not with a formula but just basically to a text. Whenever I put a text in that's not a formula I want to put quotes around it. That's telling Excel hey look that's not a minus sign. I'm not subtracting these two. I want you to put one and then add a dash and then add another and the second cell. And that'll give us this dashed relationship. And then we can simply copy that down and it'll give us the proper kind of formatting. We might have some rounding issues I won't get into here but if you want to dive into that in Excel we'll show you that too. And then we can come up with how many are in this bucket. And that's going to be a formula that looks something like this. A couple different ways you can do it but this is one way that you can do it which looks complicated but I think it makes the most sense to me to do it this way. We could say counts if and then we pick up this whole table. So we're picking up this whole table. That's the criteria range comma that's the next criteria that's this comma. And then the criteria is that it needs to be greater than this bucket. Now I don't want it to be equal to but simply greater than. I want to put brackets around that because that's basically a text and then I have to say and to get to the second the number which is in C 17 representing the 6029 and then second criteria. Here's the second criteria same table. In other words take this table and then and then what is the criteria it needs to be less than or equal to and the top range. So that whole formula is basically just saying I would like you to pick up everything that is in this column that is greater than 60.28 and less than and including the 60.51. So then if I go to the second column I'm saying I want you to take everything that's greater than 60.51 not including 60.51 up to and including 60.74 and so on and so forth. So we have a similar formula I can copy that formula down we have a similar relative formula changing just the cell references to get to this number. And so now we have our buckets. So here's the number of the buckets and here are the buckets. So in this bucket there was there was one over here or this had three in there. So here's our three. So there's the bucket with one. Here's the bucket with with three in there and so on. And now we can make this by simply selecting these two columns. We can make this into a histogram shape but by using the bar chart. So now we have our bar chart and you can see our buckets compared to what we had before look a little bit different formatting but we get the same innocence result. Now that we have it in this format we might be able to then add another chart on top of it. So so let's take a look at what that would look like if I'm just going to take an example of this if we took our height data and we imagine that this was just for men. Let's just pretend and then I created another column by just taking the prior column times point nine five. And I imagine this is for women. Now obviously the women's heights are not going to mirror exactly the men's height times point nine five. But I'm just trying to get two sets of data that are similar so that we can easily recognize them and then put them on one chart. So so I can imagine that this new set of data I can do the same thing right I can make a histogram from it. So if I just generate a histogram from that new set of data looks similar but now it's formatted. And this is the midpoint sixty point three nine is not kind of in the middle versus over here when we had all the data which we're assuming is men now is sixty seven right so so then we have a similar spread of the data around that center point because I just took ninety five percent of each data point. So it's going to look like a similar shape but the shape has basically been shifted. So now so now I would like to get these two on one graph together. So and I can't do that often as easily possibly with the histogram but I might be able to do with a bar chart more easily. So I could then create my same kind of buckets down here but now for for the women's data. Now it's kind of tricky because when I create this one I might not get the same bucket starting ending point and range. So I can adjust the ranges of these buckets and say hey I would like to give it to be twenty three point two three difference so that I can match the same ranges that were in between these buckets because what I want to do is have the two all the buckets kind of line up. So what I'd like to do then I'm what I'm going to do is say I want to keep the bucket ranges from my prior set of data which I'm assuming are the men. These bucket ranges I'm going to copy these bucket ranges over and use the same ranges because I want to still have the same X axis using these numbers and be able to map the new data for the new set of data on the same X axis. So now I'm going to say all right well then if I go here this is the same data. But and so this is all the same the same data. However I need a few more on the shorter side because the data set is going to is going to have a tail end that goes a little bit further on the shorter end. And so I had to add a few more up top. So so now we have the same data set plus a few more that are extending upwards from the prior data set. And so now so now if I do the same formula and I say Excel I would like you to count the number of this column which are between in this case 5729 or above 5729 and below 5752. These are the results we get just for the women column now. And then I can use that to create a histogram from a bar chart. So now we've got now this histogram notices not exactly like this one because I use slightly different buckets because I want my bucket ranges to match up to the same bucket ranges that I had for the first set of data that we are now imagining to be the men's set of data. But now we've got we've got that lined up now. So now we can say OK the next step is to try to put these two this one and this one on the same bar chart right. So so now notice the first thing you might think of is like well what if I took all of my data and I combined it together. In other words if I took these two columns of data and just put them one on top of each other like this is the men's data. And then I put the women's data below it in the same column. That's what this column is representing. And then I took that huge long list of data which has now been doubled. But but I had the men's and women's that we're imagining together and made one histogram it would look something like this right. Because now you've got more data that's kind of overlapping kind of in the middle and you've got you get a histogram that's that's not exactly what we want. What we want is to distinct histograms using the same buckets that are going to be mapped out you know on top of each other. So what we what we want to do then is try to say OK let's take my men's data and my women's data and what I want to have is the same the same X axis. So this X axis is the same as this X axis right so it's the same buckets and then map out the numbers which relate to those buckets. So now we have these two columns are the same which are the buckets and then we have the the numbers for the men and women into separate columns. So now I can tell Excel I'm just going to take one of these two columns as the X axis and then use each of these to line up. To that X axis as the as the why histogram shape so then we can create something like this. So now we have the buckets which we lined up together which which is pulling in from just one of these X's and then we select two data sets. So so and and you might have to go into the actual settings and we'll do that in Excel if you want to if you want to get into that more detail but it's possible to do right. So now we can say I want one set of data set for men or one set of data set for women and we can pop we can populate them on the same graph which can be a useful thing to do. So you can see how this graph you know obviously looks different than than something like this where we kind of merged all of the data together. And so again so that's how you can kind of it gets a little bit messy to do that we did we did it with a pretty complex example here because we had a pretty large you know data set a lot of a lot of groupings of the categories. But you know that's something that's how you can get kind of two histograms so you can kind of plot a histogram on top of each other which can be useful because now you have a pictorial representation. Comparing you know two sets of data and in this sense you can see obviously here they're they're symmetrical because we just multiplied one set of data times point nine five. But they're shifted and and that could be a useful visual type of tool which you can do by by making the histogram into a bar chart and lining up the boxes. So we do that in Excel if you want to check it out there.