 Statistics and Excel. Average deviation, standard deviation and variance for a population with salary 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 this CPA thinking cap, for example. CPA thinking, CAP, you see what we did 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. 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 is 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, there's three tabs down below. For example, practice blank. Example, in essence, answer key. Practice tab, having pre-formatted cells so you could get right to the heart of the practice problem. The blank tab being a blank worksheet with just our data in it so we can practice formatting the cells within Excel as we work through the practice problem. Now if you don't have this data, you could just type it in here. It's not a very long data set. You can create your own data set using techniques we've seen in prior presentations. Also check out Kaggle, K-A-G-G-L-E dot com for other practice data sets. Let's go to the first tab, the example tab, to get an idea of what we will be doing. We've been diving in a little more depth on the equations for our statistical analysis, whereas before that point we were looking at things from more of an intuitive standpoint. So we talked about the mean equation and then we wanted to think about equations that have to do with the spread of data, the standard one being the standard deviation, as well as the variance. Last time, last practice problem, we took a very simple data set so that we can go through these concepts thinking about the more intuitive average deviation and then moving to the standard deviation and the variance. We'll do a similar thing here, however now taking a bit more complex of a data set. So let's take a look at it, let's go to the blink tab, this is our salary data set. Once again on the left hand side representing wages for say a corporation. We're imagining that this is not a sample but the entire population of the data. So I'm going to delete this Kaggle thing, I'm going to hold control, scroll in a bit. So we're zoomed in to like 235, let's put a table around the data, put in our cell in the data somewhere, insert tab up top, tables and create the table, the dancing ants, do their voodoo magic dance around the cells and there is our table. Let's make a skinny bee column, skinny bee making it go down like so and then we will do some of our standard statistical analysis. So now we could of course sort the data if we want to see it from lowest to highest for example or highest to lowest and we could switch it from highest to lowest if we wanted to do that and we can now do our normal calculations. So our statistical calculations include the mean or average. Let's first format all of our cells to that I skipped that what I normally do. Let's put our cursor on the triangle, right click the entire worksheet and then format the cells and I like to go with currency, negative numbers, bracketed and red no dollar sign and we don't need any decimals at this time. We'll add them as needed when the cells call for them. So there we have it. I'll make the salary white up top so we could see that. Not all the data undo. I want to put the header, I'm going to go to the font tab and make that white and let's make it all bold too. Everything needs to be bold. All right. So now we're going to make this large and so we've got the average or the mean and then we can do the men. We can do q1 quartile one. We can do the median which is also quartile two. We can do quartile three. We could do the max and so let's do those. So we've got the average is going to be equal to you. I'm going to do this quick because we've seen it a few times in the past. Just simply using our average function, double click in the function, select in the data with it in a table. I can just select the down arrow dancing ants just around the data working their magic and doing the calculation. So then in the men equals the M.I.N. I'm going to do control shift to put the bracket or shift nine to put the bracket and then select the data. So that's the smallest number in the data set q1. I can do equals quartile quartile. I'm going to pick the first quartile option or you could just hit tab because it's on the top tab and then I can so and then I can select the data. This one has one other argument. You'll recall I got to hit a comma and say I want the one standing for quartile number one. And then not ten one. And then the median could be quartile two or we use the median function which is probably more common tab selecting the data and enter quartile three equals quartile three tab selecting the data. This time I need a comma not a one but a three because I'm on quartile three and the max equals the M.I.X shift nine and data. So there's our standard our standard data calculations right there. Let's put some brackets around this. We'll say this is going to be a home tab font group bracketing it borders and let's make it our blue drop down. If you don't have that blue it's in the more colors standard. There's the blue. There it is. Boom. And then we could calculate the average using the mean or average calculated or we calculated manual method. Let's say which would simply be our formula of I'll just pull over the quick formula here. Something like this right there's our formula. So we're going to take we're going to some we're going to sum all these up which are the X's and then sum everything up and divide by the N or the number of items right so we're just all the all that is doing is saying we're going to sum everything up we'll make this in a table format equals the S U M the most famous of functions and selecting that and then the count the count or N the number of data we can do that with an equals the count function counting it out. There's 51 of them and then we can give us the so we're going to divide divide which get hold on a second I gotta hit it's beeping at me stop beeping at me we need to divide and that's going to be the mean and so this equals then this divided by the 51 and we get the same number here as we calculated up there but we did it manually. Now let's go to the home or kind of manually home tab font group underline it let's make this like a header column home tab fonts group I'll make it black and white I'll make this blue border home tab font group border blue. Okay, so now let's make a histogram we can do a histogram out of this data. So let's say selecting the data insert and let's go hold on is that the data selected is it's yeah I think so insert and then this histogram. So here's our data histogram and it's got this 1234567891011 I'm going to change the buckets to 11 to have have it match what I have in my practice so I'm going to double click the buckets and let's just make 11 bends for whatever is to do so now we've now we've got our histogram looking like this of our data the histogram giving us an idea of the spread remembering that the the mean is that point that 71 around here is that point that if you think of it as like a teeter totter and you put the fulcrum in here that it would basically be kind of centering on that teeter totter point. Okay, so now let's do the variance so and and the standard that's due the average deviation and then we'll go to the standard deviation. So we'll start with the average and we do this fairly quickly because we saw it before we're just using a different data set now let's actually copy the data set over this let's copy all of column a selecting all of column a right click right click and copy I'm going to put that over to the side and column M right click and paste it just normal table has been input implemented and then I want a skinny in putting my cursor between the N O no no and put the in smaller so you emphasize the O no and then we're going to say that this is going to be the average deviation and I'll just put my formula in here so I won't retype the formula I'm just going to copy and paste it if you want to copy that formula and type it into excel remember you can do that by going to the insert tab making an equation and then using the ink ink item here is the easier way to kind of put it in place I'm going to make it orange so it looks a little different and maybe so there we go okay let's make this this black and white insert home I mean font group black and white on the letters okay so now now I'm going to take my data and actually I should have put my data on the other side of the of here so I'm going to take this whole column and I could drag it or cut it I'm going to right click and cut it and then let's put it over here and column Q right click and paste that looks better and then I can delete this column M and N right click and delete and so let's do that and then I can make a skinny N so the end is skinny again alright so now we'll do our average average deviation so I would compare that then to the mean so here's the mean the mean is going to be equal to I could do it this way average double click of all of this right we get to that same number we got to here so there's our mean and then I'm going to take the difference difference from every point to the middle point the mean so that's the 84000 minus the 71 and then this is where it's where it's a little bit more simplified versus the standard deviation instead I'm using the average deviation which is spelled wrong but we're going to say this is the absolute value and let's wrap the text on the headers home tab alignment wrapping the text centering it absolute value is a font formula equals ABS ABS the ABS function has nothing to do with your ABS so don't feel like it's going to help you to like work out your ABS although you know who knows but I'm going to go into my table here and then I'm going to make a total column and the table styles we're going to add the total I could over here I could take my average again if I wanted to I'm just going to put an average here on here I'm going to put my count so I'm going to say count them please and then here I'm going to sum up the differences so notice if I sum them up I still get to zero that might have been less apparent in the prior presentation but notice that if the middle point if the average is 71 498 and I take the difference of every point from the average then I'm still going to get positive and negative numbers that should add up to zero in essence right and then this is the sum function we're summing up all of the differences which we've now made absolute so that we don't have so now I'm looking at the distance from the middle point without negative numbers so then I can calculate the average deviation let's say I'll say this equals the average deviation let's see if I can average there should be a G in there somewhere and so let's say so let's make this like black and white home tab font group black white alright so now I'm going to take this is the sum of the distance from the mean which is simply equal to that 9790 and then I'm going to divide that by say that little thing and divide I think it's a comma right the count the count and then and that's N in our formula so now we've got the numerator we summed it up we took the absolute value we're dividing by N the count so we counted them to be 51 meaning 1 2 3 4 there's 51 of these items so equals 51 underline home tab font group underline and this is going to be the average deviation which is equal to the 9790 divided by the 51 can add some decimals home tab alignment or font group add decimals and there we have it let's make it blue and bold or blue and home tab font group blue and bordered or bordered blue whichever way you want to say it so that's kind of the intuitive way of doing it right so now let's compare that again to the to the variance and the standard deviation which is which is basically our default calculations for measuring this kind of average distance in essence from the middle point or mean so I'm going to make a skinny s and let's do this again skinny s I'll just copy over these formulas so I'm going to say this is going to be the variance and I'm just going to copy this formula over because we've seen it before but if you want to type the formula you could once again go into the insert tab equation and then ink it in here so you can type in stuff and it'll do it up top so that's really cool really neat I I thought it was very nice so everybody should be impressed by that I feel like and then there's a home tab font group this is should be black and white and then down here we've got the standard deviation and again I'll make this black and white home tab font group black white and we'll copy that one in and notice that the standard deviation is basically this whole thing that's that's underneath is the variance and then and that's why it's represented by a sigma squared versus a sigma so we're going to calculate the variance and then move to the standard deviation so let's make a skinny x skinny x and then we'll say we're going to copy this column O let's copy column O actually the whole column yeah well let's just copy the data I'll just copy the data in column O and we'll recreate our table I don't want the total yet because I'm going to redo it and then I'm going to put that in column why why because that's just where we landed I'll just paste it one two three to start off with and then I'll put a table around it I'm going to just put my cursor in the cell and then insert tab tables dancing ants working their magic their voodoo dance coming up with that and then we're going to compare it to the mean again so the mean equal to the average tab of the data of data not the not the header just the data boom that's our same average or mean we got over here and then we'll have the difference difference this is the same thing up until this point the eighty four thousand minus the seventy one four ninety eight we have our differences but we end up with those negative numbers we want to remove the negatives but we're not going to do it with absolute value this time we're going to square a squared which does get rid of the negatives but also leaves us with the problem that we squared everything which means that's the next step is going to take the absolute value right so I could then say this is going to be equal to the difference squared or carrot shift six to the power of two or squared enter so there we have it and so so then let's put our totals down below so I'm going to go into my table again table design in the table style options let's put our totals down below again I can do this is my average let's do an average let's do a count here let's say count it up so there's fifty one and then here we will do the sum so it sums up to zero just like before but now I have the sum over here of the squared differences right so so that removed the negative but it also squared everything so now what I can do is say since you know I've been going in my formula right so we summed up the difference but now squared it instead of taking the absolute value now we're going to divide it by the number which will give us the variance then we'll take the square root which will give us the standard deviation alright so let's do that we're going to say this is going to be the squared difference from the mean I'll just retype this in equals this number and I'm going to divide that by so divide I put that in there so I could put a divide sign it doesn't beep at me divide by the count which we're representing in our formula with an N so that equals to this cell which is what we counted that just counts everything in the table that's in column Z let's put an underline under it home tab font group under line so then we have the variance variance which is represented as sigma squared oftentimes so I can then go to my insert tab symbols and it would be under normal text Greek and Gothic and if you've been using it it'll be down here and recently used so I'll just say sigma insert it okay I need a square so I'm going to put a two enter it did some weird formatting things I'm going to go back to the first tab I want this at 11 so that too when I highlight it and it does some crazy 26 put it down to two but then I'm going to highlight just the two right click format the cells and make it subscript boom alright so I have to have the fancy symbols this divided by this so there we have it there's our variance and then we have the standard deviation standard deviation which is just the sigma then insert equation not that equation k-pa so undo what happened do it again insert the symbol of a sigma insert boom alright so this is going to be and then and now we need the the square root which is the formula equals sq square root this one sqrt of that is going to give us this to 7 and you can see it's it's going to be greater than then the number we get to with our with our average deviation right and so these numbers will make more sense when you're making comparisons of them of other data sets right so when you're comparing the variance of two data sets they make so we'll we'll get into more that later but right now we could just we'll just get through the calculations of them so I'm going to go to the home tab number and we'll add some decimals let's put some blue borders around this home tab font group border and blue and so this of course is the one that we're going to be using more often and again notice how it's kind of useful to be able to do this in a table format right you got the formula over here sometimes plug in the algebra into the formula is nice but sometimes it's nice to see you the the date of like a table format like a tax return right because then you're actually kind of breaking out the calculations step by step and some of those steps some of the sub calculations can give you better insights sometimes and also they might be useful numbers in and of themselves depending on what it is you're doing however let's also calculate it with excel here so I'm going to scroll down and we'll do the standard deviation so stand so standard deviation of the population with excel and emphasizing here that we're thinking about this as the entire population because if you say equals ST there's the standard deviation STD EV for the population versus the sample right now we're focused on the population so I'm going to select that one and then our entire data set and column Y and boom so if I add a couple decimals home tab number group couple decimals we get the same number here if I took the variance the variance of a pop you pop you lay shown that would be equal to the V a so we want the variance the AR of the population double-clicking that function and then selecting the entire data set we get our number here which matches out let's do the same thing for the sample just to be able to compare and contrast standard dv deviation for a sample sample I'm messing up my capitals in lower case but equals the the standard for the sample this time and we'll select all of our data in column Y and we can add a couple decimals just to match it up to what we did up top home tab numbers adding a couple decimals you could see it's not exactly the same as we have up top for the population we'll talk more about that later and then we've got the variance of a pop you lay of a sample equals the VA so we have the VA are for the sample selecting our data in column Y and there we have it we could see it's not exactly the same as for the population we'll talk more about that in future presentations let's select this home tab fonts group border and blue let's make this one yellow just because we're not that's kind of like not exactly what we're doing yet but we just wanted to point that out that will move to in future presentations let's check the spelling viewed review tab spelling variance the spelled wrong change it okay and there we have it