 Statistics and Excel. Wages, data, box plot or box and whiskers. Get ready, taking a deep breath, holding it in for 10 seconds, looking forward to a smooth, soothing Excel. 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 and therefore you can just open up a blank worksheet. If you do have access to this workbook, we have three tabs down below. Example, practice blank. Example, in essence, answer key. Practice tab, having pre-formatted cells within it. So you can focus in on the core component of the practice problem. The blank tab, just having the data set within it so we can practice formatting the cells as we go. Let's take a look at the example tab, the answer key to see where we will be going. We're going to be sorting our data. This data is related to wages so we're imagining we have a set of data possibly for an organization for wages that we would like to glean some insights from. We're going to do so by creating a box and whisker chart on the right hand side. That's really what we're focused in on in this presentation and then in future presentations, we will continue on to analyze the box and whisker chart with our analysis tools over here. We'll also make another box and whiskers just to note that you can put them on a side by side if you had two sets of data and then we'll compare the box and whiskers to a histogram type of format as we lead in to then starting to talk about histograms and the dynamics of them. So if I go to the tab to the right, the practice tab, we have some pre-formatted cells over here so you can more easily simply put in your box and whiskers if you wanted to do that without the formatting. And the blank tab, we just have a list of numbers. Now note that if you don't have access to this workbook, you can look up wages for a particular area if you so choose. We've talked about different methods that you can pull data from if you want to get some practice data to work with. Obviously, you can type in the data if you so choose. If you want your information to tie out exactly to this information, although I know that's a little bit tedious, one of the methods that you can use in Excel to get some data would be to use a random selection of data, so then you would want to adjust it to have some outliers and whatnot so you can kind of get an idea of what's going to happen with your box plot. So to do that you can say equals in a cell and you want to take the random between so that you can choose the bottom and top. And so you have your function here and basically most of our numbers here are between like 65 and 75. So if I took the random of 65 and then I say comma the next argument on the top is like 75 and then close up the brackets. You'll get some random numbers. And if I copy that down, I can then get a set of random numbers. And then you might want to add some outliers to it. Now obviously when you have random numbers, you can get an idea of what the outcomes are going to be because you would think they would be coherent to some randomness. Whereas if you had actual numbers in the real world, you're going to see possibly different trends that are going to be different than simply random. And those are the kind of things you're looking for, the differences between the random and what actually is in the world and what those differences might be telling us. Alright, in any case, this is going to be our set of numbers on the right. I'm going to scroll in a bit. So I'm going to hold control down and scroll in. I'm currently at 205. You can also do it by hitting this plus button over here. And then I'm going to format my entire worksheet. I do this basically every time I'm going to select the triangle button up top. I'm going to put my underlying baseline formatting and then I'll format other cells that may be different or deviant from that baseline formatting. So I like doing this by right clicking, formatting the cells. Here's my formatting box. I'm in the numbers group. I will typically format with the, I like the, actually what I've been currency, and then negative numbers are bracketed, but no dollar signs. And then I don't really need decimals here. So I'm going to remove the decimals because we're working in thousands. And then when I need decimals, I'll add them to those particular cells. I'm going to say, okay, I'll also, I'll also bold them home tab font and bold. And I think that's easier just for the presentation purposes, or you might just like the bold might stand out if you have bad eyes like me or something like that, at least, then you could use that. Let's put a, let's put a header up top. So I want to put something above up top here so I could put a header on it. So the easiest way to do that is to select an entire row, putting my cursor on the one selecting the entire row, and there's nothing on the right hand side. That's why it's the easiest thing to do because I don't need to tell Excel, hey, I would like you to move this group down. I could just say just add an entire row. So it's one less click than moving the entire thing down. I could just say insert, insert. Notice it doesn't say insert cells, insert a column because I have the entire row selected. So it inserts there and then I'm going to put up top. I'll just say wages as a header. And then I'm going to, I like to put this whole thing into a table before we create the box and whiskers. So I'm going to put my cursor anywhere within here. And then I'm going to go into the insert up top tab. So remember, these are the tabs up top, insert tab. And then in the tables group in the insert tab section, we want to insert a table. Now I only have one thing selected. So the dancing ants, I know it looks like they're marching. But if you looked at them close, they would, they'd be really grooving. They'd be grooving like their hips are moving everywhere, man, but you can't tell because we're, but in any case, if you select those, then then you can say, okay, and it'll pick up the entire table, which will result in a new table tab up to the right. So if I'm off the table, no table tab. If I'm on the table, there's a table tab. What we really want to do here is now organize the data top to bottom. That's usually one of the ways we can organize the data. If I look at this data and I just took a random sample, I just looked at people's salaries basically in alphabetical order or something like that. And I picked up their salary numbers, then alphabetical order is not going to tell me much about this, this ray of numbers. The first thing that I would want to do is say, hey, if I put this in order from top to bottom, then I can get a vision. I can at least get a visual look at what is happening. So that's clearly what we will typically do. You don't have to do that, by the way, in order to enter or create a box and whisker. The box and whisker will figure it out, the chart. But the first thing we would do when we're analyzing the data would probably be, I want to see it from Z to A. Z to A. And so now we've got the highest on top. And you're like, all right, now I can get a sense of kind of where this group of people are lying in this number set. Now the next thing we might do is start pulling some break this data down using the average, the mean, using the quartiles and whatnot. But let's first make the box and whisker chart and then we'll kind of analyze some of those other numbers in accordance with the box and whiskers. We'll also get into some more of the math on those kind of analytics later. But for now we just want to get a pictorial representation. So to do that, I can select the entire thing this way. There's some faster ways you can select, like if I click the data like that, I can select the data that way. But I'm going to include the header here too. So I'll select it. I'll just drag it down, select the data. I'm going to scroll back up again. And then I'm going to go into the insert and then we're in, so that's the insert tab. We're in the charts group within the insert tab. And now we want to look at this one here. Now notice it kind of looks like a histogram, not the box and whiskers, because the histogram is really kind of like the go to oftentimes for sorting our data, which we'll talk about later. The box and whiskers, like you can kind of imagine, I imagine it kind of like before we had the Excel and whatnot, it's probably easier to draw by hand like a box and whiskers, right? Whereas it's a little bit more difficult.