 Statistics and Excel. Histograms created with car-related data. 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 crunching numbers is my cardio product line. Now, I'm not saying that subscribing to this channel, crunching numbers with us, will make you thin, fit, and healthy or anything. However, it does seem like it worked for her, just saying. So, yeah, subscribe, hit the bell thing and buy some merchandise so you can make the world a better place by sharing your accounting instruction exercise routine. If you would like a commercial-free experience, consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com. We are in Excel. If you don't have access to this workbook, that's okay because we'll basically build these from blank worksheets. So you can start with a blank worksheet if you so choose. If you do have access to this workbook, there are six tabs down below representing three problems that we will be working, each having three tabs. Example tab, practice tab, blank tab. Example, in essence, answer key, practice tab, having some pre-formatted cells. You can go right to the heart of the practice problem. The blank tab having just the data set so that we can practice formatting the cells in Excel as we work the practice problem. Let's go to that first example so that we can see the end result that we will be putting together. We have our data on the left-hand side, which we will create in a table. We will sort the data and then we will construct a histogram from it. And then in the second problem, we will do a similar process with data related to automobiles. But a different set of data will sort the data, create a histogram from the data. We'll also take a look at how we can sort this data set a little bit differently, possibly using a bar chart as well. And we'll further emphasize that differences and similarities between a histogram and a bar chart. And then on the third example, once again, we will create another histogram. Now, as we create these histograms, we want to start to get a feel for how they look and how basically we can describe the histograms as being basically skewed to the right or to the left. So let's go back to our blank, first blank tab. Our data set is on the left-hand side where we have automobile names and the miles per gallon for them. Now, if you don't have this data set, you can look up the data. If you so choose doing a Google search for it possibly, you can make up the data set. You could type in this data set. I believe I got this data set. We got this data set from the Kaggle, which is K-A-G-G-L-E dot com. So you could check it out there if you want to check that out. Once we have the data set, I'm going to then format the entire sheet like we do every time. I'm going to put my cursor on the triangle, right-click on the data and format the cells. And then I'm going to go into the currency. This is my default formatting typically as an accountant basically. I'm going to go into the bracketed or negative numbers, bracketed and red. I'm going to remove the decimals and the dollar sign. We don't really need the decimals because there's no decimals in the baseline data. So I'm going to say, okay, I like to go to the home tab, font, also make it bold so possibly it stands out a bit more. I'm going to hold down control, scroll in a bit on the scroll wheel. So I'm currently at the 205% on the scrolling in. So then I'll select my data and maybe I'll center the headers, go into the home tab, font and then center in the headers. And then what I'd like to do often is make a table generally. So then I'm going to make a table of my data. I can put my cursor anywhere in the data set if there are no blank cells. So if there was a missing piece of data, then that often causes you problems. You'd rather have a zero populated than having a missing cell. So no missing cells. So I'm going to go back up to the top, put my cursor anywhere in the data insert tab and then tables group and make a table from it. So now the dancing ants are going around the table. The range is from A1 to B399. So I'm going to say, okay, boom, table has been made. Then we're going to sort the table. So we've got the name on the left. What we really want then is the miles per gallon, right? Because that's the data that we're going to use to get an idea of basically the average miles per gallon, for example. And then our histogram related to it. So I'm going to click on that, sort it from A to Z. And so now we've got our calculations from lowest to highest, right? If I flipped it the other way around from Z to A might be a little bit more, you know, that looks nicer. You know, you got the higher number on top. In any case, I can then let's make C skinny. I'm going to put some data in column D. So before I do that, I want to make sure that I don't type something right next to the table. It's better to make a skinny C because Excel likes to separate stuff out. Just like, you know, when you're eating your mashed potatoes and like then there's some peas or something on the plate. And you don't like them to get all mixed up. You know, you got to have a separator like those plates that have a separator between them. So then I'm going to say that this is the average and we'll say the average function. I tabbed by the way on the keyboard to get to cell E2 equals the average. So our common calculation and I'll double click on it. And then I'm just going to put my cursor up top. So I select just the data and there it is. So we might often just pick up the average 24 and let's pick up the median, the middle number. Which is the one in the middle, which you always think of Rocky Balboa and his boxing coaches advice. Mickey says he sees three of them out there. So Rocky hits the one in the middle. We got to hit the one in the middle. That's what we do with statistics. We'll select the median and we'll go into this one and select it. And so there's the median. So those are common numerical calculations. Now let's just add our histogram. So with the histogram, I just want the data set because I want to see how many of these numbers fall into a particular bucket. So I'm going to select the data again. Insert tab up top charts group and then insert the histogram. So here's our histogram going to pull this on over and I'll delete the title. And so there's our buckets that were chosen for us. If I select the buckets and I go into my tab to the right, automatically selected buckets. Their bandwidth is 3.7 for 11 buckets. That looks pretty good. If I wanted to make it like two or something, we could see a different spread of the data. So now you've got a bit, you know, different spreads. And you could see if I made it like one that might be going too far, although it looks kind of interesting. If I make it higher, making it like 10, you could see a very different kind of look of the data. So how compact do we want the data to be? Let's bring it back to like two. It's kind of an interesting one. And then if I close this back out, now you could see the data kind of is populating around this bucket right here. And you can see that it's basically leveling off towards the right. So typically when we see that, when we try to describe the data, we're going to say it's going to be like skewed towards the right. Let's bring it back to the auto so these buckets are not so many buckets. I'll bring it back to the auto. And so there it is. So you see a nice kind of skew to the right. So you've got these added buckets out here. So that would be a common term that we might use would be that skewed to the right. Let's look at the second set of data and do a similar process. I'm just going to go back onto this one. This one, we have the car name and the cylinders. So the cylinders. All right. So let's put our cursor. There's no missing cells here. There's no missing cylinder data. And if there's no missing cylinder data, I could just that's a pretty long data set. So what I would like to do is just put my cursor in the data set and add the table anywhere in there. Don't select two. Just select one and then add data. But before we do that, let's do our other formatting. I'm going to select the entire sheet with a triangle, right click, format the cells. And I'm going to bring this to currency, negative numbers, bracketed and red. And then no dollar sign, no decimals because we're not having a percentage of a cylinder or anything. So I'm going to say, okay. And then I'll hold down control, scroll in a bit. I'm at 175% home tab font group. I'm going to make the whole thing bold. Put my cursor somewhere in the table, not selecting two cells, but just one. And then insert tab tables, inserting the table, dancing ants, selecting properly the data. It looks like from a one to be 399. So I'm going to say, okay. And so there we have our table. Then of course we can sort it with our sort field smallest to highest. So three is the smallest. If I sort of the other way, then it goes up to eight. So I can then do our calculations on that. I might say I might want a few calculations. I might say let's give us the average, average equals the average double clicking on that calculation or that function, putting my cursor on the dropdown to select the data. There's the average. If I take the mean, I could say, okay, let's take the mean or the mean is the average. Take the median, median equals the median double clicking the median. Selecting that range. And so now we have the median. I might take the max and the men, by the way. I might say what's the highest number equals the max, which is a useful function to know. So I'll just type that in here to remind us that that is something you can do. So eight and then the men, the smallest number equals the men of our data double clicking the data, selecting it double clicking the function, selecting the data. There's the smallest and you're like, oh, look at that little card. So cute with the three. The three says it's not cute. It's weak. It's weak. Anyway, I don't know. I'm going to let's put a histogram in there. So if I select the data and we're going to say insert and then charts and we're going to put the histogram in place. So let's add a histogram and there's our default histogram. And so you could see the spread of the data and looks a little bit, you know, kind of spread out because of course you'd have to know kind of the idea of how many cylinders, you know, might go into a car. And to generally get an idea of the distribution. Now notice when you're talking about cylinders, you might not need the bucket. It might make more sense to just list the number of cylinders and say do a bar chart. So for example, if I if I click on this, let's just go to the tab on the right. And then I go and I say it's currently at the bandwidth of 0.18. And that's not bad. So we'll keep it there. But you might say, hey, look, it might make more sense to just count the number of cylinders and then possibly make a make a bar chart on it. So I might just say, let's just do that. Let's say, let's say I say cylinders and then I'm going to count the cylinders. So I'm just going to make a nice little table down here and count the cylinders. So I could say, well, let's just let's just say one, two, three. I'm going to copy that pattern down to eight. So I'm selecting the three of them, putting my cursor on the fill handle, dragging down and Excel can clearly see that pattern and say, OK, you're counting by ones. I get that. I get it. So then you take it up to eight. And then I could say I want you to take the data that we have on the left and count it on the condition that it's a one, a two, a three, a four. And we can do this pretty easily because we don't have any kind of one and a half cylinders or any or, you know, we have just a specific number between one and eight. So I'm going to say, all right, let's say equals count. And then notice we have the count function, which would just be count the cells if there's something in it. We've got the count if and the count ifs. In this case, we only have one condition. So I want to say count if on one condition. So I'm going to take the data set, the range. So I'm going to select this range, take that data and then the next argument comma to get to the next argument is the criteria. And I want to say if it's equal to this number. Now, because I'm just putting it equal to that number, I don't have to say equal. I'm just going to, I'm just basically saying when you find something in this range that is that number, then pull it in place. So I don't need quotes or an equal sign or anything, which is nice. I should then I could close it up, but I don't think I have to. I could say enter and it'll still pick it up. Of course, there's not any with zero in it because then the car wouldn't move. A car wouldn't move or what or one would be pretty slow even still. So we have to have at least, you know, you got to have at least like a couple and three in there. So I'm going to put my cursor on the fill handle and drag it down. And so hold on a second. Something funny happened. You could tell something. No, that's right. Is that right? Hold on a second. That's right. I don't know what I'm talking about. So there it is. So now you've got, so now you've got your, your account. Now you could make this into a table if you so choose. I could make this into a table by selecting any cell within it. Go into the insert tab tables and make a table out of it. If I wanted to. And then, you know, I could sort my data by the count or by the cylinders. And now I can make a histogram, but using a bar chart. So then I can select this data. And I'm going to select both of those, those, and I'm going to go to the insert up top. And we're going to go to the charts. And we're going to now not make a histogram, but the bar chart. And we can put that in place. And maybe you want a fancier one. We could, we could make a, like a, like a fancy one this way if we wanted to. But let's just do the normal bar chart. And then notice it's trying to pick in both the cylinder count. And the count. So what I want to do is I don't really want the cylinder data in here. You could see that if I go into the design group and the data set. So I can see the cylinder. So I'm going to uncheck the cylinder and then okay. So now I've got the count from one to eight. So that's the number of cylinders on the X. I probably don't need the legend down here anymore. I could just click on it and delete it probably. Or you could take the legend off over here. And so now we've got, we've got our count that way. And remember that, so I've basically created another kind of histogram. Now, if you wanted to change the width of these cells, you might have a little bit more flexibility when you, when you make like a bar type of chart, you might be able to have a little bit more flexibility with it with it. So I could make the width of these different, for example, if I click on these items and I say a double click on it. And I want to say the width, the space between is smaller. So it looks more histogram-y maybe. So I can bring this down a bit. Let's do it this way. And I'll hold it down so you can see it like that. All right. So that's another way that you can do it. And then of course, some more, the way you have more flexibility is that you could actually then make your, make your count and just remove the ones that don't have anything in it. So possibly we wanted to do like, like another one, a cylinder and then the count. But we're going to start at, at three over here. And then, and then count it down. I'm just going to recreate the table. And so then we can count it down this way. And again, I'll make a table out of this, insert tables, boom. And then I can select that data and I can insert not a histogram, but just a bar chart. Let's do a fancier one this time. So now I've made this kind of be be aware of the 3D stuff too, because that's another way that people can kind of make the data look a little bit deceiving. I think you could select the data over here and just delete it. That's another way you can delete the second data set and then delete that. So there's another way that we can kind of show the data in, in that format, which could be useful. And then let's go to the last one. And let's just do this one more time. So this one is the horsepower. All right. So I'm going to select the entire data set again. I'm going to right click and format the cells and go to currency, negative, no dollar signed, no decimals, and then okay. And then I'm going to whole control, scroll up a bit. So there we have it. I'm going to make the whole thing bold in home tab font group and bold in the whole thing. Put my cursor in one of the cell. Let's make this top part center just so it makes sure it can see it's a header. And then put my cursor in one number, home tab, or not home tab, from the home tab to the insert tab, tables group, add the table, dancing ants going from A1 to what is that B399. And then okay, table has been inserted. So let's just make it a different color for the fun of it. And now let's make the horsepower go from either Z to A. So there it is weak. That's from A to Z, then Z to A. Whoo. That's too much. That's going, you've gone too far this time. Let's close up the cell, the C. That's too many horses. You're going to get trampled with that many horses in the room. Start to smell like horse dung if you have that many horses. So let's say that we're going to say the average equals the average. Double-clicking on the function, selecting the data and enter. So then we have the median equals the median. Double-clicking, selecting the data, the median, the max. What's the highest one? Equals the max. Double-clicking the max, the max, the mad max. You're mad if you're going on that max, mad max. And then the min equals the min. All right, and select, so those are common calculations. Let's enter the histogram. So I just want the data set over here for the histogram. So I'll select the data, insert charts, histogram, and there's basically our default histogram. I'm going to delete the title and make it a little bit larger. If we look at the buckets for the histogram, then it gave us, these are the buckets. We could go to a more customization on the buckets if we wanted to possibly adjust in this to like, what if we brought it down to five? You get a much more detailed look of it, possibly most likely two detailed, 10. And if you went too far the other way, 20, then it might be a little too condensed. If we go to like 50, then you get this very skewed looking histogram. So a big difference in the number. But let's go back to the auto. And you can see this is basically kind of skewed to the right. You know, you've got this tail going out. It's sometimes people get confused when you say it's skewed to the right because you might say, well, the fatter part is over here. So maybe it's skewed to the left, but really you're looking at that tail that's kind of dragging down here. So we'll say that that's going to be skewed, a data set that's going to be skewed to the right. So there's some, just some examples of it. Now, next time, maybe we'll take a look at some where we can, where we apply two sets of data to get like a scatter plot to see if one is affecting another, if one set of data is affecting another. So we'll take a look at some scatter plots possibly in future presentations.