1 00:00:00,066 --> 00:00:01,999 2 00:00:02,000 --> 00:00:08,000 Hello and welcome to a video on mean and standard deviation in Excel. Today we'll be going over a 3 00:00:08,000 --> 00:00:14,200 couple of different ways you can do mean and standard deviation including long-hand calculation and 4 00:00:14,200 --> 00:00:14,233 5 00:00:14,233 --> 00:00:20,299 also using shortcuts in Excel like functions to do the 6 00:00:20,300 --> 00:00:20,333 7 00:00:20,333 --> 00:00:26,033 calculations more quickly. So we'll be using two IVs today to do these examples. 8 00:00:26,033 --> 00:00:26,466 9 00:00:26,466 --> 00:00:32,632 The first one we'll be talking about is average, or mean, to do a mean first 10 00:00:32,633 --> 00:00:32,966 11 00:00:32,966 --> 00:00:36,566 we need to find the sum of all of our values. 12 00:00:36,566 --> 00:00:39,866 13 00:00:39,866 --> 00:00:46,199 So we'll do that really quick. And I'm using the function equals sum to find 14 00:00:46,200 --> 00:00:46,233 15 00:00:46,233 --> 00:00:52,466 these. Now the next step would be to divide by n, 16 00:00:52,466 --> 00:00:52,666 17 00:00:52,666 --> 00:00:58,966 which we have 170 observations in our data set. And we know this because 18 00:00:58,966 --> 00:00:59,499 19 00:00:59,500 --> 00:01:05,500 we go all the way to row 171. But we have an 20 00:01:05,500 --> 00:01:11,933 index here. So we don't count that first row. So equals 21 00:01:11,933 --> 00:01:11,966 22 00:01:11,966 --> 00:01:20,599 our sum divided by 170. It gives us 6,396.036. 23 00:01:20,600 --> 00:01:21,533 24 00:01:21,533 --> 00:01:27,666 But I can also just use average and select 25 00:01:27,666 --> 00:01:28,532 two IVs. 26 00:01:28,533 --> 00:01:35,666 27 00:01:35,666 --> 00:01:38,699 And you can see we get the same number with a lot less work. 28 00:01:38,700 --> 00:01:42,233 29 00:01:42,233 --> 00:01:48,466 Okay. So that's how we find the average. We can just use equals average. What 30 00:01:48,466 --> 00:01:54,599 if we wanted to find standard deviation? Well, if we wanted to do this the long way, we would need to find 31 00:01:54,600 --> 00:01:54,633 32 00:01:54,633 --> 00:02:00,066 x minus x bar squared, which we could do over here. 33 00:02:00,066 --> 00:02:00,732 34 00:02:00,733 --> 00:02:06,599 Now we've already found x bar, it's this average right here. 35 00:02:06,600 --> 00:02:10,133 36 00:02:10,133 --> 00:02:16,633 So we're going to say equals open parentheses x minus. 37 00:02:16,633 --> 00:02:17,266 38 00:02:17,266 --> 00:02:23,466 And now I'm going to select x bar in a unique way. We're going to say dollar 39 00:02:23,466 --> 00:02:29,466 sign j, dollar sign 3. And that's going to tell 40 00:02:29,466 --> 00:02:35,266 Excel not to move from that space that we always want it to select this number 41 00:02:35,266 --> 00:02:35,832 42 00:02:35,833 --> 00:02:41,866 for our calculation raised to the power of 43 00:02:41,866 --> 00:02:41,899 44 00:02:41,900 --> 00:02:47,633 2. That upward carat symbol means raised to the power of in Excel. 45 00:02:47,633 --> 00:02:47,933 46 00:02:47,933 --> 00:02:54,466 So if we wanted to square something, we would do that carat too. If we want to cube something, 47 00:02:54,466 --> 00:03:00,532 then it would be carat 3. We'll hit enter and I'll double click so it 48 00:03:00,533 --> 00:03:00,566 49 00:03:00,566 --> 00:03:06,866 goes all the way down. Now the next step in finding 50 00:03:06,866 --> 00:03:13,166 a standard deviation is to sum all of our x minus x bar squared values 51 00:03:13,166 --> 00:03:13,199 52 00:03:13,200 --> 00:03:16,066 and then we 53 00:03:16,066 --> 00:03:23,599 54 00:03:23,600 --> 00:03:24,000 divide 55 00:03:24,000 --> 00:03:29,766 56 00:03:29,766 --> 00:03:33,366 by n minus 1 divided by 169 57 00:03:33,366 --> 00:03:37,132 58 00:03:37,133 --> 00:03:43,166 because remember we have 170 in our data frame and now we square root 59 00:03:43,166 --> 00:03:49,132 that. So sqrt that function is square root. We're going to select that value and 60 00:03:49,133 --> 00:03:49,766 61 00:03:49,766 --> 00:03:56,199 we get 2018.7219. Now there is a function in excel 62 00:03:56,200 --> 00:03:57,900 63 00:03:57,900 --> 00:04:04,300 which will let you find the standard deviation. We're looking 64 00:04:04,300 --> 00:04:04,333 65 00:04:04,333 --> 00:04:08,433 for two IVs, a standard deviation. Okay and 66 00:04:08,433 --> 00:04:10,866 67 00:04:10,866 --> 00:04:16,766 this time it's rounded it slightly. There we go. 68 00:04:16,766 --> 00:04:20,799 69 00:04:20,800 --> 00:04:27,000 Looks like this one's also rounded. There we go. Now we've got 70 00:04:27,000 --> 00:04:32,933 the same number for both of these results. 71 00:04:32,933 --> 00:04:35,666 72 00:04:35,666 --> 00:04:41,899 So obviously you're probably going to want to use the quickest way possible to do this. And that is what we expect you to do on your 73 00:04:41,900 --> 00:04:47,900 homework. We want you to get comfortable using equals average and equal standard deviation. But just know 74 00:04:47,900 --> 00:04:53,966 that you can absolutely do it by hand. If you really want to to or felt the need to or 75 00:04:53,966 --> 00:04:53,999 76 00:04:54,000 --> 00:05:00,133 if you wanted the practice doing standard deviations for something like a test coming up. 77 00:05:00,133 --> 00:05:00,766 78 00:05:00,766 --> 00:05:06,866 All right, that should be all you need to know about mean and standard deviation to do your homework. Have fun with with Excel.