Basic Statistics

Quick reference

• 00:04 In this video, we're gonna look at some basic statistical functions and
• 00:08 how they differ between formulas, so let's start with count.
• 00:12 We'll say, =COU + Tab, and we'll grab the count of the supplier costs.
• 00:19 Close the parenthesis, we'll hit Enter, and it comes back with 7, why 7?
• 00:24 Well, there's 2, 4, 6, and 7, so this makes sense.
• 00:27 Now though, I'm gonna copy this little formula across the row, and
• 00:31 you'll notice that we go 7, 7, 6, and 6.
• 00:34 Well why are these two different when there's the same amount of rows?
• 00:39 And the first one is that, there's something that appears to be blank here
• 00:42 in the third column, and obviously, that doesn't get counted.
• 00:46 Also, this #VALUE error, also does not appear to get counted,
• 00:51 which means that it's only counting proper numeric values.
• 00:55 Let's look at the next function, COUNTA.
• 00:57 So we'll go with COUNTA + Tab, and we'll grab the same information.
• 01:03 So we're gonna close the parenthesis here, once again, we get 7 and I'm gonna drag
• 01:07 that across, and you'll start to see that we have some different values.
• 01:11 Notice that we have 7 in all cases here.
• 01:14 Now the reason being is because this cell here is actually not blank.
• 01:18 There is a space in it, so I'm gonna press the Delete key.
• 01:21 And what you'll now see is that my COUNTA drops to 6 again,
• 01:25 and my gross profit here for my account comes to 7, why?
• 01:30 Well, the error is gone, the space was causing an error,
• 01:34 a value error, which was messing up this formula.
• 01:38 What's happening now is that blank values are actually not counted.
• 01:42 COUNTA counts text and that's why when there was a space in the cell,
• 01:45 it was actually returning the count.
• 01:47 It's something to be aware of.
• 01:49 Let's look at AVERAGE.
• 01:51 We'll say, =AVERAGE and we'll grab
• 01:56 our cells, and we get 38, which we'll copy across.
• 02:02 Now, I should probably spin these up with a decimal point because
• 02:06 a mean average basically takes the sum of the numbers and divides it by the count,
• 02:10 so there's usually a decimal point that's on that.
• 02:13 What if we wanted to get the maximum from each column?
• 02:16 Well, let's go with =MAX.
• 02:19 We'll grab our values again, close our parenthesis, hit Enter, and as you
• 02:25 might expect from your first column, you get a 125 because it is the largest value.
• 02:30 So we'd expect to see 200 and 25, and 50 as we carry this across.
• 02:36 And just like that, that's exactly what we get.
• 02:39 So what about the minimum?
• 02:41 We can get that with the MIN function.
• 02:43 MIN + tab, we'll grab our column again, and you'd expect that even though
• 02:47 there's two 3s in this column, that that's exactly what we should see.
• 02:53 And that's exactly what we get.
• 02:55 We should also expect to see a 7, but what's gonna happen in column E?
• 02:59 Will it give us a 2, even though there's 3 of them, or will it give us a blank?
• 03:04 Well, let's find out.
• 03:06 We'll drag this across, and notice that it picks up the 2.
• 03:10 A blank is seen as an empty cell, and is therefore not calculated in the formula
• 03:13 result in this case, so it's not gonna actually bring that back.
• 03:18 And what about MEDIAN?
• 03:20 Well, median is a different type of average, and
• 03:23 if you're not familiar with the differences between min, median, and
• 03:26 mode, it's probably a good idea to do a quick review of that on Wikipedia,
• 03:30 because these are definitely useful in different scenarios.
• 03:33 So let's go and pick up the MEDIAN here and see how this actually changes.
• 03:37 And you'll notice that it gives us 25, and 25 is the median,
• 03:41 is the middle value from the dataset.
• 03:44 And when we go and we copy this across now, you'll see that our middle value,
• 03:48 when you actually rank all these on a number line, comes up as 40.
• 03:52 Our middle value for our remaining column here is halfway between 2 and
• 03:57 4 because that's where the number line is.
• 04:01 And this one here, the middle value is 13 when
• 04:03 you rank them all up on a number line and take the one right in the middle.
• 04:07 Finally, we can grab the MODE.
• 04:10 The MODE being, not that one, we're looking for MODE.
• 04:14 There we are, and the MODE is the most frequently occurring value in the dataset.
• 04:20 And when we go and hit Enter, you'll see that it's 3 in the first column, and
• 04:25 when we drag it across, it's 58.
• 04:28 Shows up twice, so it's picking the largest and the most frequently occurring.
• 04:32 And we have 2 because it shows up three times, and
• 04:35 we have 13 because it shows up the most as well.
• 04:38 So there is few different statistical functions that you can use inside Excel.

Lesson notes are only available for subscribers.