 Statistics and Excel, combining two histograms on one chart, part number two. 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 Accounting Rocks product line. If you're not crunching cords using Excel, you're doing it wrong. A must-have product because the fact as everyone knows of accounting being one of the highest forms of artistic expression means accountants have a requirement, the obligation, a duty to share the tools necessary to properly channel the creative muse. And the muse, she rarely speaks more clearly than through the beautiful symmetry of spreadsheets. So get the shirt because the creative muse, she could use a new pair of shoes. If you would like a commercial-free experience, consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com. Here we are in Excel. If you don't have access to this workbook, that's okay because we basically built this from a blank worksheet. However, we started building it in a prior presentation starting 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 on it so you can get right to the heart of the practice problem. The blank tab is where we started with just our data set so we can practice formatting cells within Excel as we work through the practice problem. So in prior presentations, we took our data set which is related to heights. We made a table out of it. We created a histogram with it which has a pretty nice bell shaped type of curve to it. Given the fact that we're dealing with heights which you would expect would have a nice kind of center point and then taper off in this type of format. And we have a pretty large data set that we are drawing from. Then we wanted to get to the point where we can have two data sets on one chart, two bell curves or two histograms. So in order to do that we made our own bucket sizes and then created another histogram but this time we created it not with the histogram tool but with the bar chart tool. And so now we want to create another data set so that we can practice putting the two data sets together on one chart. So to do that I'm going to take this data set, the whole thing, I'm going to copy column A. So I'm going to put my cursor on column A, right click and copy the entire thing. I'm going to bring that on over to a tab over here. Let's bring it on over to like AA for example. Let's put it on Z. Right click and I'm just going to paste it normal because it's hard coded numbers so that looks good. And then I'm simply going to imagine that this data set is just for men. Now it's not necessarily the case that that is the case but I'm just going to imagine that's the case and then I'm going to put the next one is for women. Now I just want to make a data set to practice with so I'm going to just take this every number over here and take 95% of it. So that's obviously not an actual data set, a sample of the data. What it will give us if we imagine the graphs and it'll shift the whole graph over but the shape will be much the same. So that's what we would imagine to be the case. So what I'm going to do to do that is I'm going to say equals left to the left once that 6850 times 0.9595% and when I hit enter it'll populate the entire area given the fact that we're in a table format. So now I've just made a whole other data set which is basically just shifting the whole bell shape if we were to graph it. So let's go ahead and graph it now I'm going to use just a normal histogram just for the new set of data. So I'm going to select the entire data set go into the insert tab charts and then I'm going to create a histogram just to see what that looks like. So here's our histogram going to make it larger and just kind of compare it. So we can compare this histogram to what we made on the other side. I made this really wide last time. So I can compare that to this histogram right? So this histogram is taking the full screen. Let's make this one a little wider maybe so that they're kind of okay. So you know you've got basically the same shape but the center point of this one is around 67 to 68 and then the center point of this one looks to be around 64 to 65 or so. So it's basically got the same shape but the center point has been shifted due to obviously what we did with the data set. Now the other kind of issue that we run into is that the buckets might be different right? So if I look at the bucket sizes over here then obviously Excel is going to choose a different bucket range to try to optimize the bucket. So it had a .23 difference and 65 buckets. If I go on to this chart where let's check it out. I'm going to click down here. This one's got a .22 difference. So I'd like to make that bucket size difference like the same. So I'm going to go into the bin width and I'm going to make this .23 just to have the distance from the buckets the same. So now you've got kind of the width or how far apart the buckets are from 60 to 6048, 6025 to 6048 is the same distance as these buckets. The buckets aren't exactly the same but we have similar kind of buckets which will make it more comparable. Now what I'd like to do is put this other set of data into this histogram and have them showing two sets of data on the one histogram. But that's not always easy to do using a histogram. That's one reason you might use a bar chart. So in other words, if I went on this data right here and I went to the chart designs into my data tab and I look in my data. I can see, I just added that one, if I see this is series one and that's going to be the data that I just picked up. You might say, well, can't I just add another data and say this would be for men. The other one's for women and then take this set of data and then say, okay. So now I've got my two data sets but it's not actually changing the graph here. So one way I can do this is try to make this into my buckets again. So we did this before. We made our buckets. Now what I'd like to do is have the exact same buckets that I have over here on this side as well and just extend the buckets so that they also include what needs to be included for the new data set. Which is going to go down possibly lower on the lower end. So let's go ahead and see if we can copy this. I can copy these. I'm just going to take this whole bucket series. I'm not going to copy the results but I just want to copy the bucket ranges. So I'm going to say copy. I said control C. I'm going to put them in the same kind of area which is next to the chart. So it's in C17. I could put that over here in 17. I would like to put it up here. I'm going to squish this up a bit and 17 and paste it. So there we have it. So now we've got our same kind of series of data here and then we've got our formulas. So now what we need to do is extend the low end to take it down to this around 5749 for example. So I'm going to have to pull this down a bit and extend the top bit out because this is going from lowest to highest. So a couple of ways we can do that. I'm going to just select this whole range and just kind of drag it down. I'm going to select the whole thing and say let's actually drag it down. I'll scroll back up. Now we could cut it and paste it. So notice it's the same thing to put my cursor here and drag it down as it is to say right click and cut and then paste it down here a bit. That's doing the same thing. We can also insert cells above it if we wanted to and push it down. Like say I want to insert cells above that. I can't insert a whole row but I can select those cells and say insert and then I want to shift the cells down. And so that's another way that you can kind of move them down. Now when I when I made these references notice we started here at the bottom bucket. And so that's kind of like our starting point. Let's make that yellow. And then I added point two three up as we went up. So now I'm going the other way I need to subtract point two three. So I'm going to say this bucket is equal to the prior bucket minus point two three. And this bucket is the prior bucket minus point two three point two three. And so then we can copy that up and I'm going to copy it up till I get to like that 57. So I'm going to copy these up and see how much room we have 57 06. We just need to make it go down to 57 49 around. So the buckets aren't lining up exactly but 57. Let's do it right here. So it's not going to look exactly like this. Histogram the bucket ranges but we have the similar distance between the buckets that we're trying to line up between our two data sets. All right. So then I'm going to copy this up as well. So I'll just take this formula and copy it up and then boom I'll get rid of the yellow. We don't need the yellow here. That's too much yellow. There's too much yellow going on round here. I'll make this a little bit larger and then I'm going to make my my formula for this data set. I'll redo the formula. So I'm going to be pulling from the woman's data set and we want it to be if it's greater than if we wanted to count the number of data is if it's greater than 57 29 and less than or equal to 57 52. So here's our if formula again. I'll try to scroll in a bit so that we can see it a little bit more clearly. So I'm going to say this equals if or no sorry count if and then multiple conditions. So I'm going to have an S on that ifs and then double click the formula or add the brackets the range. So I'm just going to pick the range. There's the range and then I'm going to say comma and then the criteria the criteria needs to be greater than so I need to put like text in there. So I have to put quotes around it greater than quotes around it and then add which I need to do with an and and then the cell reference which is this cell. So everything that's greater than that cell that's the first condition comma second condition criteria range. So the criteria range selecting the women again and then comma. So what's the criteria. So this time it's got to be quotes less than or equal to because if it lands right on that number that will put it in this bucket in the quotes and then and attaching this number. So there's the argument. So it's got to be greater than 57 29 count it count these numbers if it's greater than 57 29 or less than or equal to 57 52 is what this argument is saying. So I'm going to say OK and nothing's in that first one. Alright let's copy it down though putting my cursor on the fill handle and copying it down copy it down copy it down copy it down. And so there we have it. And so now we've got some action happening here in the middle range. So same formula just copied down to these two cell references took the same range. So now I can now I can add make I can mimic this histogram. It's not going to be the exact same because we made the buckets a little bit different but we made the distance between each bucket the same. So possibly I don't need to select the whole range because I got a lot of zeros down here because this data range was what was included in the and the men's number. So there is you know there's more on the tail end on the upside. So maybe we could stop the histogram you know like around here. So what I'll do is I'll select the data if I wanted to mirror that histogram we could select the data from here down to like. Here let's say let's pick up one zero and then I don't need anything below that. And then this time I'm going to go to the insert but charts group but not a histogram but rather the bar chart the bar chart boom. All right. And so let's pull that to the right and we can compare. I should have scrolled up before I put it in place. Now I got to scoot it up like this. Oh it's so annoying. Okay. And then I can pull this one over and then so now we can kind of see him on top of each other so we can compare and we can contrast. It's the same thing. Why do you have to say both compare and contrast one implies the okay whatever. You have to say both them because it sounds cooler when you compare and contrast otherwise you're just doing one thing you're just comparing. And that's like easy if you do two things like compare and contrast then you're doing more work. All right. So if we click on the actual data that will open up our format bar if I go into my my series options I can lessen the width between these. So if I put it like 5% you know it looks more like histogram. Histogram you like and then and then I could go up top. I'm on the chart I could go into the the chart here and we could add the chart labels. So here's our labels I could put those it only gave me the one label. Hold on a second I got to select the chart itself. Let's undo that last label. Sometimes it's a little finicky because I only because it thinks I only have that one thing selected. So I'm going to try to click off of that thing go to the chart details and then say now data labels up top. And so there we go. And then I can format those data labels going into the data labels and getting rid of the decimals possibly. So if you go into clicking the labels label options and then it's in general I want to make it to currency or number. And then I have the option of removing the decimals zero decimals. And so that's a little cleaner. I could put the data labels up top on this one as well. And we can go insert not insert chart design data labels. So let's put them on top again. So we have a little bit of a difference here because again the bucket sizes aren't exactly the same. But the point is here now I can take I can take these two data sets and basically combine them together on one chart. Which is obviously super cool something that you know everyone's looking forward to. But we're running long on time so we'll do it next time. So stay tuned and come back next time when we'll put them together. So I'm going to make this a little bit smaller. Let's clean it up while we're here before we do that. And then I'll make this let's make this blue and bordered blue and bordered make this blue and bordered. Actually that one could be yellow maybe because that was our midpoint. And then this could be well not the midpoint that was where we had to start adding more data or something. Brackets and border put some borders around this one. Is there anything else we can clean up here. We might the Y column. Why is the Y so wide is what I want to know. Why is the Y so wide. Let's let's make it smaller. Skinny why a skinny why why because it's too wide. And then we're going to go down here and say if the if the it's we're looking at height data for a height of that height you shouldn't be that wide. For that tall for that height the width should be less. An average home tab font group. Let's put a blue and borders around this one. So that looks good movie B to the end. All right so next time we'll come we'll combine the data and we'll see what happens if I combine all the data in one histogram versus you know if I if I try to put the two data sets like side by side on a histogram. .