Basic Statistics

Quick reference

• 00:04 In this video, we're going to look at everybody's favorite subject, statistics.
• 00:08 And we're going to do a little bit of analysis on this table of car parts here.
• 00:13 And we're going to go and calculate these different stats.
• 00:18 Now, I want to be really clear that there is an error inside this spreadsheet.
• 00:23 This is deliberate, and
• 00:24 you're going to see what kind of effect that has on our outputs.
• 00:27 So the first thing I'm going to start with here, is I'm going to go and
• 00:31 take a count of the values inside the supplier price(cost) column.
• 00:36 Now, if you look here, there are two, four, six,
• 00:39 there are seven values that we actually have here.
• 00:42 So we're going to see what we get back.
• 00:44 But before we do this, just to make my life a little bit easier,
• 00:47 I'm also going to just very quickly go and lock in the rows as absolute.
• 00:52 This is going to allow me to copy these formulas down the table,
• 00:55 but still point to this.
• 00:56 And yet, after returning my result of 7,
• 00:59 I can also copy these across the table right now,
• 01:03 because this is now going to point to the list price, sale price column.
• 01:07 What I want you to recognize about this particular function here when I did my
• 01:12 count, is that we had seven values in this column, seven values in this column.
• 01:18 But this one has returned a count of six, as has this column.
• 01:25 Why?
• 01:26 Well, because blank values and
• 01:29 errors do not count towards the results of the count function, okay?
• 01:34 So count only counts numeric data.
• 01:37 Blanks, values, and text don't get counted.
• 01:41 What I want to do now is I want to extend this out to this other function that
• 01:45 we have called COUNTA.
• 01:46 So I'm just going to change this to COUNTA.
• 01:48 You'll notice it's pointed to the same data range as what we had before, okay?
• 01:54 And again, it returned 7, just the same as the count function did.
• 01:57 Let's copy this across the table.
• 02:00 Notice now, we've got 7, 7, 6, 6.
• 02:03 These last two columns here didn't flag anything or
• 02:06 missing these items as far as their count goes, but COUNTA picks them both up.
• 02:11 Why?
• 02:12 Well, COUNTA, I like to think of as count all, it counts numbers and texts,
• 02:17 it even counts errors into its results.
• 02:20 So this comes back as there are seven total lines of items here.
• 02:22 But now, I want to actually fix something in this dataset.
• 02:25 Okay, this is the thing that I want to point out.
• 02:27 This is not actually blank.
• 02:28 Somebody's put a space in the cell.
• 02:31 Now, if I go and delete it,
• 02:32 what I want you to watch is what actually happens here.
• 02:36 So I'm going to delete this.
• 02:38 And you'll notice at this point in time that we've got,
• 02:44 the cell here now returns 6, not 7.
• 02:47 This one still returns a 7.
• 02:49 And this guy now return 7, not 6, because it's not having an error here, so
• 02:54 it's actually picking up a different count, okay?
• 02:56 So at this point, COUNTA is actually giving us the same values in both places.
• 03:00 The difference is that if I were to go and throw something in here like an a,
• 03:04 it counts textual elements, okay?
• 03:06 So it's going to have an effect on what your outputs actually are.
• 03:10 Let's go and pick up the average now.
• 03:13 So we go and grab this, the average.
• 03:15 We use the average function, and this will generate the mean average for the dataset,
• 03:20 in other words, sum of the values divided by the count of the values.
• 03:24 And what you'll see here, we come up with the mean average of 38, 57, 7, and 14.
• 03:32 If we want to pick up the max, we would use the MAX function.
• 03:37 And it's nice that these things are actually named somewhat logically in a lot
• 03:41 of cases, not all cases, to be fair.
• 03:43 But in this one, it comes back and it says, hey,
• 03:45 125, there is the maximum value.
• 03:48 So in theory, if we copy this across, we should see the maximum value of 200,
• 03:52 25, and 50.
• 03:53 It's not the fact that they're on the same line,
• 03:57 it's the fact that they are the maximum value in that column.
• 04:01 All right, so if I go now down and copy this one down,
• 04:04 let's go change this to MIN.
• 04:06 That's the minimum function.
• 04:08 And this honestly is the big trick when you're working with Excel,
• 04:11 is trying to figure out what's the name of the function that I want?
• 04:13 So try typing something that's logical, and if not,
• 04:16 always a Google search will help.
• 04:17 But in this case, we've got min, that's returning 3.
• 04:21 The next one, we've got a 7, the minimum value's 2 and 2.
• 04:25 So these are the same values in this particular case.
• 04:28 What about our median?
• 04:31 So if we go down and grab this one here, the median gives us
• 04:36 a different average than what we get for our mean average.
• 04:41 So the median is something that actually occurs in the middle of the dataset.
• 04:46 So you basically order up the data and cut it straight down the center,
• 04:49 the middle value.
• 04:50 And that's why if we ordered all this stuff,
• 04:52 you'd see that the middle value is actually 25 here.
• 04:54 We've got, 3, 3, 22, 25, 40, 45,125.
• 05:00 In this one here, it's going to be 40.
• 05:02 In this one, it's going to be 3, because we've actually got a middle
• 05:06 value between 2 and 4, so it splits the difference evenly.
• 05:10 The last one that I want to get is mode.
• 05:13 Now, this one's a little bit trickier, because when you go and
• 05:16 actually end up typing in the mode function, I'm going to do this just
• 05:18 straight from the worksheet grid here, so we're going to go with mode.
• 05:21 And what you're going to see is that we actually have three functions for this.
• 05:25 MODE.MULT will actually return multiple values to a cell.
• 05:27 We're not ready to do that yet, that's going to come much later.
• 05:31 MODE.SINGL will actually bring back a single mode value,
• 05:35 the most frequently occurring or repetitive value in the range of data.
• 05:39 You'll also notice down the bottom here,
• 05:41 we've got mode with this little triangle on it.
• 05:44 And if you notice, this one says this function is here for
• 05:47 compatibility with Excel 2007 and earlier.
• 05:50 It will still work, but
• 05:51 you don't want to use this if you're building a new spreadsheet.
• 05:55 So in this case, I'm going to grab MODE.SINGL, hit Tab, and
• 06:00 now we're going to go and grab this data here.
• 06:03 And the reason that this MODE.SINGL exists is because the Excel team rewrote the mode
• 06:08 function to give a better statistical representation that was more in line with
• 06:12 what industry standards were expecting.

Lesson notes are only available for subscribers.