 Statistics and Excel. Standard deviation versus 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 that's okay whatever because our merchandise is better than their stupid stuff anyways. Like our, trust me, I'm an accountant product line. It's paramount that you let people know that you're an accountant because apparently we're among the only ones equipped with the number crunching skills to answer society's current deep complex and nuanced questions. 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 basically built this from a blank worksheet, however, starting in a prior presentation. 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. The blank tab, we started with just a blank sheet so that we can practice formatting the cells within Excel as we work through the practice problem. Let's do a quick recap of what we have done in prior presentations as we have been calculating the average deviation and the standard deviation where we will now be thinking about the comparison between the two and why we might be using or its standard to use the standard deviation as opposed to the average deviation even though the average deviation is a little bit more simple and it's a little bit more intuitive in some ways. In other words, we kind of thought about the process of what if we wanted to come up with a number and we didn't know about the standard deviation and we're trying to just intuit and think from scratch how we would do it to represent the spread of the data, the dispersion of the data around the center point, the mean. The first thing we would probably do is start to think of something like the average deviation. We would say if this is our data set, we only have four data points, the average of this data set is zero for this particular data set, summing them up dividing by four. If I take each of those data points minus the mean, this is the distance from the mean. However, I have negative numbers here so what I would do then is just simply take the absolute value giving us the distance from the mean 20 and then just dividing by that. So we've got the average deviation, right? We took the average of the distance from the mean to give us an idea, a number to give us that dispersion, that spread kind of idea. Here's the histogram of our simple data set, the mean at zero. We've got data points on the negative and on the positive side of that zero. We compare that to the standard deviation, which you could break out to that everything under the square root sign being the variance and then this is the standard deviation. So now what we are doing, we're going to do the same starting point, we'll take a comparison to the mean, but instead of taking the square root, instead of taking the absolute value that is, we square it. So we have that same kind of characteristic of removing that negative number, which is important, but now we've squared it so we of course come up to a bigger number, the 104 versus the 20 when we sum it up. So, and then we can divide by the count, which is four, and that would give us the variance, this number, and then if we take the square root, that gets us to the standard deviation. This is for a population calculation as opposed to a sample. Notice it's similar, but it's typically higher than what we have when we did our average deviation. But you get a similar kind of calculation, a similar concept, and then you could of course do the same calculations with Excel formulas with the variance for the population and the standard deviation for the population. So now the question is, well, why would I use this one instead of this one if this one is actually a little bit more intuitive, a little bit more simple, because we would typically want to have things more simplified if possible in math usually unless there's a reason. Well, we can see that this calculation gives us the same kind of idea and it removes the negative numbers, which is nice, and it also gives us basically like a unique number. And so let me drill down on that a little bit more. So let's compare and contrast this as though we're comparing every point to something other than the mean for the average deviation. So here's what I mean. I'm going to make a skinny AA. I'm going to put my cursor on the data over here. I'm going to copy the data and I'll paste it on AB. I'm going to make it into a table again. It looks like a table, but it's not a table because it's just looking at the format of the data. Insert tab up top, tables. Let's put a table around it. And so there we have it. And then I'm going to compare it instead of to the mean. I'm going to compare it to a point one, which is not the mean. That's the key. We're not comparing it to the mean. We're just going to compare it to one instead of the mean. So let's compare each one equals the one above it to one. I'm going to copy that down. So instead of comparing to zero, we're comparing to one. We're going to take the difference. And so this is going to be equal to the negative six minus the one instead of the mean. And that gives us our differences from the point of one. So then we're going to take the absolute value because we're looking at the average. So we're using this format. Taking the absolute value. So I'm going to say absolute value absolute value is going to be equal to ABS. That's the function for it. Double clicking on the ABS. And that's going to give us just flipping the sign to all positive numbers in essence. So there we have it. Let's add a total column down below and go to our table and table style options. Adding the total. And then on the data, let's make this one the average. So that came out to zero. So we're not picking the average. We just picked a random point of one instead of the average to be comparing to. And this is the count. Let's do a count here just counting one, two, three, four. And this one, let's do the sum. So I'm going to sum these up. And we don't come out to, you'll note, zero as we typically would if we're using the point of the mean. Because the mean is the middle point by definition. We didn't choose the middle point. So now we come up with differences that in this case add up to a negative four. But the absolute value of them still adds up to 20, which is the same thing when we did it this way, right? So if I was then to do my calculation down here and say that we're going to say this is the sum of distance from point instead of the mean from the point one is going to be equal to the 20. We're going to divide that by the count. So we'll divide by the count, which is n, the number, which we have as four, that's the same. And I'll underline that font group and underline, and that's going to that's going to give us our result. I'll just say this is going to be equal to the 20 over four. And let's add some decimals home tab number add some decimals we get to the exact same number of five. Even though we took we took the count from one instead of instead of from zero. So we're not getting like a distinct number as as by by by using the mean versus any other point, right? We could do it for another point like if I let's go ahead and put some brackets around this and make this blue. Let's do it again. I'm going to copy the whole thing putting my cursor on the skinny column. So we'll copy the skinny all the way to AE right click and copy. And then I'm going to put that on AF right click and paste. So there we have this. And now let's say let's just do the same thing, but we're going to say point number two. Right. So now we and you can see that I just changed that one number and we get the same the same result. The sum of the different distances is now eight. If I take the absolute value from point two instead of zero, the mean or point one, I still get to 20 and I get the same number. Right. And let's just do it one more time. If I copy from the skinny to AJ right click and paste, then I'm going to make this one a three. Right. And we get to that same number five. Now if I instead do the same kind of test, but but I use my my standard deviation calculation. Let's do the same thing. Let's copy this over. Let's just copy this part of it over. I'm going to or let's I'm going to right click and copy. And then paste that here. Right click and paste. So notice this is not a table anymore because I only copied part of the table and I don't need this bottom bit. Let's delete. Well, let's I'll just delete what's in it. And then let's enter a table in here. So I'm going to I'm going to delete this total row to because I'm going to have to re-input the total row. So let's actually delete this. I'll just say delete and let's go home tab format. I'm just going to format the cells like that. All right. So I'm going to go to the insert to tables and insert a table. So there we have it. Let's make this a little bit smaller. All right. So now instead of now we have the difference. Now we're going to take the squared. We're going to take those amounts and square them. That was the differences between the absolute versus they're going to be squared. Let's say squared. Hopefully that's spelled right. This is going to be equal to one above it. The one next to a carrot. That's how we represent to the power of with a carrot to which is squared. Enter. So there we have it. Let's put our totals back in table design. We're going to go to our totals. So let's say this one is going to be the sum. So it comes out to zero. But I'm going to go back to point number one again. So let's go to point number one. Let's change my headers. This should be point number three. This should be point number two. So now we're using one instead of zero. So if I add those up, if I count these, we're going to say give me a count. That's going to be four. If I look at the sum of the differences, it comes out to 12 instead of zero. If I use the mean here, zero, and I look at the sum of the differences, it's not picking up this calculation properly. This equals this minus this. There we have it. It would be zero. Okay, so then I'm going to change this to one. And now I come out to four. That looks correct. And then over here, squared, if I sum up the squared items, then it comes to 108 instead of what we had when we did this with zero, which it was 104. So now let's do the bottom bit here. I'll go ahead and maybe I'll try to make this easier. I'll copy what we did over here. So we've got the square difference, this whole bottom bit. I'm going to copy that and I'll put it over here. Right-click and paste. So now as we copy that over, however, notice that this is no longer the variance as we have defined it or the standard deviation because we're not using the mean, but rather this point number one. This is pulling from the wrong table. So I'm going to say this should be coming from this table. This should be coming from this table dividing them out. That looks correct. And the point is that we now come out to the 5.2 instead of what we had before, which was 5.1. So you can see the point is by using the standard deviation, we come up to basically a unique value when we use the mean as opposed to any other point and we did the same kind of variation from a different point, which is we don't have that same characteristic when we take the average. So if I did that again, if I copy this again, I know I did this kind of quickly, but if I go from A, the skinny to AT, right-click and copy and I paste that in AU and paste and make this a number two, number two. So now if I look at my total, it comes out to 120 and so that's coming from the right table. So now we come up with a value of 5.48. So notice that's another kind of reason why it might be more beneficial to use the standard deviation to use this formula, standard deviation, rather than possibly a more intuitive formula of the average deviation because the standard deviation gives you that kind of unique value if you're calculating around the mean rather than any other point. I'm also just going to remove here the variance and the standard deviation because these aren't the variance and standard deviations as defined by the formula because here we've changed the mean, which was zero to point number one in this case and point number two in this case just for our demonstration and comparative purposes.