 statistics and excel average deviation get ready taking 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 is better than their stupid stuff anyways like our CPA six-pack shirts a must-have for any pool or beach time mixing money with muscle always sure to attract attention yeah even if you're not a CPA you need this shirt so you can like pull in that iconic CPA six-pack stomach muscle vibe man you know that CPA six-pack everyone envisions in their mind when they think CPA yeah as a CPA I actually and unusually don't have tremendous abs however I was blessed with a whole lot of belly hair yeah allowing me to sculpt the hair into a nice CPA six-pack like shape which is highly attractive yeah maybe the shirt will help you generate some belly hair too and if it does make sure to let me know maybe I'll try wearing it on my head and yes I know six pack isn't spelled right but three letters is more efficient than four so I trimmed it down a bit okay it's an improvement if you would like a commercial free experience consider subscribing to our website at accounting instruction dot com or accounting instruction dot think of it dot 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 three tabs down below 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 blank tab blank worksheet so we can practice formatting the cells within excel as we work through the practice problem let's go to the example time to get an idea of where we will be headed we're going to create a very simple data set on the left-hand side to practice concepts which will lead up to the concept of standard deviation but before we get there we're going to start with the average deviation which is probably more of an intuitive concept and will give us a sense if we were to kind of create these from scratch or an idea of how they might have been created over time so we'll calculate the the average deviation and then in a future presentation use those concepts to continue on with the variance and standard deviation all right let's go to the blank tab to to check this out and so let's start by formatting our worksheet the way we do every time i'm going to select the triangle up top to format the worksheet right click on the selected area format the cells and then i want to go to the currency negative numbers bracketed and read remove the dollar sign let's get rid of those decimals as well so there we have it and and i'm going to pull in some of the calculations or formulas we did in the past i won't recreate them because we've created them in the past but this is our you know average calculation probably don't need it at this point but i'll pull it in anyways i'm going to hold down control and scroll up a bit and let's just create our data set so i'm in cell a one just going to be our data set hold on i want to make everything bold i got to make everything bold so i'm going to select the entire worksheet again and go to the home tab font group and we work bold here we do things boldly everything is bold uh so any case negative you're too bold way too bold yep so let's go ahead and uh put our data set very simple data set we'll be working with so we have a negative six a four a positive four and a positive six now obviously that data set will uh will net out to zero we're kind of doing that on purpose so that our mean calculation will be at zero at that this point that's going to be the idea let's go ahead and put a little table around our data set so i'll put my cursor in a cell uh that's in our data set insert tab up top and totals and then we'll put a table within it dancing ants working their voodoo magic dancing around the cells and there we have it i'm going to make column b a little bit wider so it's so our formula is down there and i don't kind of run into that formula when i start working on stuff let's do our mean calculation the mean calculation we're getting mean man this is average average no more messing around anymore we're getting mean with the mean calculation get it because it's a mean count okay we're going to say this is going to be equal to let's do it with the average so this is going to be basically our formula down below where the average calculation is going to add them all up and divide by the number that are there right so if i select this little data set it'll add them this plus this plus this plus this divided by four right and that's going to give us zero because when we add those up we have zero so our mean is at zero let's go ahead and make that blue and bordered which is our custom dropping it down on the borders and blue and then on the bucket if you don't have that blue if you want to find that blue you can use a different color but i like that blue standard color wheel there's the blue that's the one that's the one all right let's do the uh the actual calculation let's do a mean manual calculation just to practice that one more time make sure we understand the concept i'll say calculation and so let's make this a header tab so we'll do a little table calculation home tab font group making this black and white and then i'm just going to sum it up so let's go with the sum of data which is x so i'm just going to sum up this data set which is summing up like the numerator up top which you can see in these two ways we can see it equals the sum our most famous function if you know nothing else you want to know the sum function summing it up comes out to zero and then we're going to divide by divide by i'm going to say i'm going to put this character so that when i put a divide it will not try to try to do something because the divide is a mathematical uh function although i don't think the divide would do anything anyways but just a note if you're trying to just type something and you're starting with something like a plus or a minus then you could put that first and and see it doesn't show up it's just telling excel this is not a formula i want you to just type this thing out we're going to divide by the count i'm going to make the i can count them myself there's four of them but i'm going to use the count function to do it because that's way cooler and it helps us practice so put the dancing ants and work in their magic with the count function four of them home tab font group let's put an underline so we can get to the mean we get to the mean this equals this divided by this so zero divided by four i don't want to get to the mean i want to get to the nice that's two you got to get through the mean before you could get to the nice so we're going to then go up home alignment let's do some indentation and double indent home alignment double indent we'll put some blue borders around this by going to the home tab font group border it drop down and making it blue okay so now that we have that i'm going to give us a a calculation a new formula which we're going to call the average deviation so remember the mean is telling us kind of like that middle point and we have different kind of calculations to do that but now we want we want to kind of think about the spread and so we so another calculation is going to be the standard deviation but we're going to kind of gradually get into that first thinking about what we might do intuitively which is going to be the average deviation so let me let's actually practice writing in this formula i'm going to make this black and white for the header and i'm going to enter the formula we're going to type in the formula and then we'll kind of work it out in excel so if i go to the insert tab and go to the symbols if i make an equation let's do our equation again i'm going to make it with an ink equation so ink equation in the tools so equation tab tools ink equation i'll make this a little bit larger and let's type this thing in so it's going to look like this we're going to say we're going to use that some thing so it looks like this and so there it is even though it wasn't very nicely put i'm going to put the n on top it might not see the n at first but once i put the i equals on the bottom it will usually pick it up so i equals one and so there it is so that looks good and then i want to take the absolute value so when i do the absolute value it'll probably read it as a one but then it'll see it when i finish it so i'm going to put absolute value sign so see it thinks it's a one but then when i put inside it i'm going to put x i x sub i and so and then minus the u which is a mu and that that stands for the mean and we're going to put another uh so absolute value symbol and then i'm going to put this whole thing over underlining n okay so there's our what is that that looks like a funny it did something funny hold on a sec we just want an n n there we go okay so what is it so what so if we're trying to think about the the the spread of the data the first thing that we might do which is an intuitive thing to do is to take a look at all these data points right we could take a look at these data points and kind of think about where each of them lie in relation to the mean the middle point uh the the zero so so that's what this is saying we're going to say we're going to take each of these data which is going to be represented by x so uh x uh is going to be you know six four four and six taking each of those minus uh the mean which is now represented by the mu here and so that's going to give us our distance of each of the data points from the mean now we purposely made some data points positive and negative so that we can take into consideration this issue of what if we have negative data points that's that could cause us kind of a problem uh so what we what we want to do is is is not have positive and negative data points but see the distance from the mean whether it be positive or negative and that's why we take the absolute value so right so these are all going to be the mean is zero so these are going to be positive and negative from zero so it's going to be the absolute value will give us a positive number no matter what so it's going to be six four four and six right and so and then we'll take that we'll sum those up and then we're going to take that and divide by the count which is which is n so this is kind of an an intuitive the first thing that would probably come to mind when we're trying to you know deal with this concept of of of of the spread right we could we could start to think of a calculation like this let's make the home tab font group let's make it a little bit larger and make it orange this is a drop down orange all right so there it is okay so now let's do this let's actually do it so the numerator is going to take all of our data minus uh minus the mean so i can make that with a nice little table so i can be we can do that quite nicely in excel so i'm going to make uh column e smaller put in my cursor between e and f and making it smaller and then i'm going to take my data let's just copy the table over here i'll just copy it right click and copy it that'll be my starting point right click and paste and then i'm going to i'm going to compare each data point to the mean i'm going to compare it to the mean which is equal to that zero now when i hit enter it's it might try to take the cell below it see it's all zero but it's trying to take the cell below it i don't want it to do that so i'm going to put my cursor in here double clicking make it an absolute reference you can do it by selecting f4 in the keyboard or simply typing a dollar sign before the d and the one the dollar sign not representing dollars in this case but telling excel don't move the cell references down keep on pulling the same cell of uh this zero up here in d1 you only need a mixed reference by the way with just one dollar sign but the absolute is easier to remember so uh that's why we'll just use that so there we have it and then let's take a look at the difference so now we have the difference and the difference is going to be equal to the six in this case negative six minus the zero so the distance is six but this is now still giving us a negative number now we picked the mean to be zero on purpose so that we can kind of easily see this issue with negative numbers and positive numbers right so so now we have to say well i don't want negative numbers i want to measure the distance from the mean whether it be positive or negative that's why we would say if we were trying to think this out intuitively we would say well this makes sense we're taking each value minus the middle point which we're designating as the mean and and now we're going to but but this doesn't work if there's negative numbers so i'm going to take the absolute value right so we're going to say let's do absolute absolute value now i'm going to scent i'm going to center and wrap these headers so i'm going to select these headers home tab alignment wrap them and then center them okay so then now the absolute value the way to do this in excel there's a formula equals abs now obviously if you don't know that formula you can clear you can just look it up right you could type you could google search how do you do absolute value in excel and it'll and you know you'll find the formula but absolute value of the six because excel's quite a lot of people use it so there's so finding out those those kind of simple formulas is pretty easy to do if you have internet connection so there we have it so now we've got the absolute value now because i'm in a table i can add a total column so let's add a total column by being in the table going to table designs and then the table style options i'm going to add a total column over here so in the total column the data if i was to add up or sum the data let's hit the drop down it gives us our options of what we're going to do by default let's sum them up so it comes out to zero of course and then the mean calculation maybe i'm going to use my count this time so i'm going to say count the data just to show me how many data options because there's no point in summing up the the mean which is the same number all the way down and then the difference i'm going to sum this one up which is going to come out to zero now these two look the same because the mean is zero but they won't always be the same if the mean is not zero as we will see in future problems but the difference will always add up to zero uh when you sum it up because the differences from the mean which is the middle point will always result in in amounts that are going to add up to zero okay and then and so then we're just going to and over here we're going to uh we're going to sum them again so we took the absolute value of the difference and now they're all positive numbers so we get up to a positive 20 so once we have that we could say okay then the average average deviation calculation according to our formula let's put a i'll put a uh header column here home tab font group black and white and let's say this is going to be the sum of the distance from the mean right the sum of the distances from the mean uh let's put it over here is going to be equal to that 20 so we took the distance from the mean summed it that's going that's basically our numerator and then we're going to divide by i'm going to put this here and then a divide sign divide by the count which we're counting as which we're recognizing as n so we're going to say the count and i did the count up here which is four i'll pick it up right there in our table there's four items one two three four and let's put an underline here home tab font group underline and that'll give us our average deviation so our average deviation is going to be 20 divided by the four so we get five let's add some decimals just so we can compare it to what we'll do next time i'll add a couple decimals and so there we have it so that so that would be like an kind of an intuitive calculation right when you're trying to when you're trying to think about well how can i get some some calculation that will give me an idea of the spread of the data well you could say well if i think of the mean as the middle point then what we'll do is we'll take all of our data and we'll compare it to the middle point so that we can then see the distance of each item from like the middle point and the sum of those should add up to zero because the middle point's in the middle and that's how we calculated the mean and then we can take the absolute value of those distances and divide it by the number to get to get kind of a number that gives us an idea of the spread and that and so that could be a kind of a numerical way for us to represent that now this isn't the way we normally do it in practice though because we usually use the standard deviation which has a little bit more of a little bit more complexity to it and we'll try to explain why maybe it would have more complexity because often because normally when you think about mathematical equations you're usually saying if it's simpler that's the one to use right there's got to be a reason why you would make something more complex in order to use it because the because math is supposed to be elegantly simple right so you want to so we'll try to say well why why don't we just use the average deviation versus like the standard deviation so we'll get into that a little bit but of course this will also kind of lead into you know the same concepts will be used in the standard deviation which we'll do next time all right so let's make this blue and blue and bordered home tap font group blue put some borders around it let's do a spell check just to make sure everything is uh as is spelled right or at least spelled good enough so spell check wouldn't catch it okay so there we have it