 Statistics and Excel. Combining two histograms on one chart, part number one. Get ready, take it a deep breath, holding it in for 10 seconds, 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, but that's okay, whatever. Because our merchandise is better than their stupid stuff anyways. Like this CPA thinking cap, for example. CPA thinking, CAP, you see what we did with like with the letters? And this CPA thinking cap is not just for CPAs either. Anyone can and should have at least one possibly multiple CPA thinking caps. Why? Because based on our scientific survey of five people, all of whom directly profit from the sale of these CPA thinking caps, wearing this CPA thinking cap without a doubt, according to the survey, increases accounting productivity tenfold. Yeah, at least. Yeah, apparently the hat actually channels like accounting energy from the quantum field ether directly into your head. Allowing you to navigate spreadsheets faster. It's kind of like how in like the matrix when Neo learns kung fu, or at least that's what the scientific survey's saying. So get one because the scientific survey participants could really use some extra cash. 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'll basically build this from a blank worksheet. But 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 can get right to the heart of the practice problem. Blank tab, just having the data within it so we can practice formatting cells as we work through the practice problem. Also note, if you don't have access to the data set, you can take a look at Kaggle K-A-G-G-L-E dot com as a source to look for some sample data sets. This one's related to height. We're going to go to the tab on the left, the example tab to get an idea of what we will be doing. We'll format our data into a table, calculate the average, and then we'll make a histogram from that data, given the fact that it's data about height, we would expect it to be somewhat bell shaped as it is here. Then in order to put two sets of data together, we're going to recreate our histogram with a bar type chart. So we'll format it in this format so we can make a bar chart, the bar chart being easier to overlay another chart on top of. And then we'll take our current data set and we'll kind of imagine that this is for men, and then we'll do another calculation to make a similar data set for women, just so we have some numbers to practice with. And then we could imagine that if we put those two data sets together, it would look something like this, but what we really want to do is try to create something like this. So now you've got basically the two histograms that you can see are distinct from each other and kind of offset it as we can see here. All right, so let's go to the blank tab. We'll build this in a few presentations. I'm going to get rid of this Kaggle in the middle here and let's start off by just formatting our entire worksheet. That's our starting point typically. So I will then select the tab up top, the triangle, right click on the worksheet on the highlighted or selected area, format the cells. I'm going to go into the currency, negative numbers to be bracketed and red, get rid of the dollar signed. And here you'll note that there's multiple decimal places. So maybe I want to keep the decimal, but I'll round it to two decimal places, which will round off some of these numbers simply to do two decimals. So I'm going to say, okay, that looks good. Then I'll bold the whole thing, home tab, font group and emboldened the whole thing. All right, I'm going to hold control and scroll up a little bit. So I'm currently at 265 on the zoom in. Now I'm going to create a table around our data set. So I'm going to go to the insert tab to do that tables and create a table. And so it's selecting the entire data set with the dancing ants doing a mamba around it. You can't see again, you can't see their hips shaking or anything. But if you were to zoom in, they're totally their hips are shaking and they're like they're getting into it when they're dancing. So just it's not like a march. You would think they're marching, but they're not. They're totally into, they're totally like dancing. Any case, I'm going to put a space in between these two. I'm going to make this a little bit smaller and then wrap the text up top. So we're going to go to the home tab and alignment. Let's wrap the text and center it alignment and center. So now we could sort the data if we wanted to. And this is we have a fairly long data set. Notice that this is a pretty extended amount of a sample of data. So it's still a sample of data. So we're taking all of this data set as a sample of data. And if we had it's down to 25,000 out of the population of a population. So if we take that and we make a histogram from it. So I'm just going to select the whole data set by putting my cursor on the drop down. Now I've got all the data selected and we can go to the histogram. We've seen in the past just go to the insert tab charts group drop down on the histogram. And we want a histogram. Boom. And it makes a pretty nice histogram here. I'm going to get rid of the title. And then as we would expect with data like this. That's about I'm going to make it a little larger height. We get a nice distribution because we have a very large, you know, sample of data that we have. So we've got the buckets that have been made down below. So we've got our our buckets. And so 60.28 to 60.51. I believe these are in inches. So if you wanted to, you know, divide by 12 to get the feet or whatever. So if we wanted to convert to feet, you know, and so the so this middle point seems to be around 68. So so again, if you wanted to convert this to feet, it's be something like 60 68 divided by 12. You know, 5.67. So in any case, that's here. So all right. So then so now note when I when I look at this plot, it's difficult to add another data set basically on top of this one, right? If I select the entire data set, then then, you know, I can add another set of data set in it. But if I want to lie something on top of it, it might be useful to for us to format the data in such a way that we can make a bar type of a column chart instead of a histogram. So we want to make a histogram using this tool instead of this tool. And then we'll be able to possibly put the two sets of data on top of each other. So let's first think about how to do that. How can I recreate this histogram? Well, I can I can look at my data sets down here. I'm going to make a skinny B column to make a skinny B. And I'm going to make this a little bit wider. And I'm going to mirror my buckets down here. So I've got the buckets from 6028 to 6051 and then 60.74 to 60.97. We could change those bucket sizes, by the way, by clicking on the histogram and then adjust the bend width, if we so choose, which is currently at 0.23 difference. And we have 65 bends. So let's imagine down here that I'm going to have then let's do one more down. I'm going to say 60.28 and then it's going to 60.51. So I'm just copying this 60.28 to 6051. And then the bend width, we can see is 23 difference. So if I go up, this is going to be equal to the one above it plus 0.23. Should give us to the 6051, which is basically the ending bucket over here. And then I'm going to do the same thing this way. I'm going to say this equals this plus 0.23. And so I'm going to select those two and copy them down. And so now I've got the 60.74 here to the 60.97. And so if I keep copying that down, I want to copy it down till I get to the 75 to the 75.23. So I'm going to put my cursor on these two. I'm going to put my cursor on the fill handle and drag it down. And so if I let go, I'm at 65. I'm going to keep on going till I get to 68. And so I'm going to go to there's 69.71. We've got a lot of buckets here, a lot of buckets. So we'll keep going down to 75. So 75 is the endpoint. Is that right? It goes from 75 to 75.23. So if I copy one more down, 75 to 75.23. So there's our buckets. So now what I'd like to do is make a similar kind of tab that's going to be on the x-axis that looks like this, having the range. So we can do that with a little formula. I could type in here. If I don't want a formula, I could put this apostrophe and then I could say this is going to go from 60.28 and then dash maybe 60.51. However, that's kind of tedious to copy that to type that all the way down. So what I could do is do a formula which has text in it by saying equals. And I'm going to point to this cell and then say we want to say and because I want to connect it to another cell, not add it or divide it or create a function to it, but connect it. And then I'm going to put quotations for the text that's going to go in the middle, which is just going to be a dash, end the quotation, and then we're going to say and and then this one. So what this is saying is equals pick up what's in cell C17 and just put what's in there in it and then and connect it to because I have a text field, we put the quotations around it, a dash between the two and connect it to what is in cell D17, which is that 60.59 and enter. So there we have it. I'm going to make this a little bit larger. So there we have it. Now we might end up with a problem as I copy this down, but that's my general formula. I can take this and copy it down. And so there we have that boom. Notice it did something funny down here. So now we've got this issue that it did something funny. So how can we fix that? We could we could say that we wanted to round it to get rid of these decimal places. So for example, if I go into this data right here, if I was to add if I was to add decimals, it's coming up to something that's actually not exactly rounded to two decimals. So I would like to tell Excel, hey, round it to just two spaces. So the way to do that, I'm going to double click on this cell. I'm going to go to the front of it and put in front of it a round function. So I'm going to say round and then brackets. So now I'm putting that in front and now I've got my argument. There's the argument, which is correct. And then comma, I want you to round it and then to round it to two decimal places. We use a 10. So the 10 represents rounding it to two decimals. So I'm going to then close that up. And by the way, when you use this function, it gets a little bit tricky to use the 10, you know, how many decimals you're rounding to. But once you get used to it, then, you know, you can it's not too bad to round that out. So then I'm going to do the same thing for the first one here. I'm going to put round brackets and then comma and a 10 brackets and enter. So now we've rounded those two datas. And if I copy that cell all the way down, it looks like a little bit more complex of a formula. But then it takes care of that rounding problem. All right, something happened here. I'm going to copy this one down. Okay. I think that's good. Okay. And then I'm also going to do the rounding thing on this one. I should do it from the top down, but I'm going to say equals equals round. Whoop. Hold on a second round brackets. Scroll into the left and then comma 10 brackets. And then this one, double click round brackets. Go into the left comma 10. All right. So there we have it. So now I'm going to make column E a little bit wider so we can see all the numbers in there. Now that I have my brackets, I want to then select the data from this data set that is in between each of these buckets. And it's used so we have the beginning and end. So I'd like to have a formula that's going to say something like if the number is above this number, but below this number, then you want it to be in this bucket. And so also just realized that you could see this number is the same. So we want it to be including if it's 6051, we want it in the top bucket and the bottom bucket. If it's 6051, we don't want it here. We want it upper in the top bucket. All right. So let's do our formula in E17. It's going to say equals count if, and I'm going to say ifs with an S, double click the formula. Here's our criteria bar. So if we could highlight that, we could see the criteria. Criteria range. I'm going to select the entire range up top with the drop down. There's our range, comma next argument is the actual criteria. So we're going to say that this needs to be, I'm going to use a greater than. So I have to put the quotations quotation, greater than quotation. And then and I've got to use an and connecting it to the 60. So it's got to be greater than the 60. That's our first argument. And then comma, the second criteria that we needs to have is the same range, criteria range to same range, selecting the entire range. And then the criteria for that range, comma, is going to be criteria two has to be then quotations less than or equal to, meaning if it lands on 60.51, it's going to be included here, close up the quotations, shift seven for the end, and then finally the 60.51. So kind of an intimidating function, but it gives you a nice calculation. We can copy that down. And it should copy the relative references all the way down. We could do that by just double clicking. And it should copy the relative formulas all the way down. So this is picking up everything that is greater than not equal to, but greater than the 62 81 up to and including the 63.04. Then we could use this data set to create a histogram, but with the bar chart. So now what we want to have is on on the x axis, we want this stuff and then this on the y. So I'm going to select these two columns. And I'll scroll back up to where I want the histogram to be before I enter it. And then insert tab and we go to the charts and graphs histogram. There's our histogram. Boom. Now it doesn't look. Whoops. Sorry, not a histogram. Hey, wait a second. Insert tab charts. This time we don't want the histogram. We want the bar chart. All right. The bar chart. We made a histogram with the bar chart. So let's move it down here. And then I'll close this for a second and make this a little bit larger. And so now we can kind of see these on top of each other. So here's the histogram we made by just inserting a histogram. And then here's the one we made with the bar charts. So there we have it. And so then if I select the data, if I double click on the data here, and sometimes we might want to make the width smaller to like like 10 maybe. So now it looks more like histogram. And we could add our data fields. So I could go up top. Sometimes it's a little finicky to add the data numbers. So if you go up top to the, so I'm in the chart design and then the ads up top. And I want to be adding then the chart labels that I could put in here or up top. There's our chart labels. We could put them up top here. So if we had a wider chart, it'd be a little bit, we need a quite wide chart to pull in that many data labels. But, you know, we could put those in there. We might format them maybe. Well, I'll keep it like that. We'll make a really wide chart. So we'll pull this out here. I'm going to hold shift, scroll in a bit. And just see if we can make it wide enough. We could get rid of the decimals is what I was thinking on the data labels. But in any case, that gives us an idea. We could do that up here too. If I pulled this histogram to the right, and we wanted to kind of compare the data labels on this one, I could go into the design and the charts. And we can access the data labels, put them up top here. So this one nicely rounded for us. So in any case, there we go. And so here's the data label. So if you select the data, and you go into the labels over here, and into numbers, and you format it from general to a number formatting, then you can remove the decimal places, which could be a little bit cleaner. I'm going to remove the title up top. So there we have a histogram that we made with the bar chart. Now, so next time what we will do, and notice the heights are, this one I just expanded, it's a little bit taller this way. So I mean, I can do that, it squishes it down a bit so that it looks similar to our two histograms up top. But so that's what we'll do. Now next time, what we'll do is think about, well, what if I had a second set of data and then I wanted to create two histograms that are kind of on top of each other. That's why this tool might be useful. So we'll experiment and continue on with that next time.