Basic Statistics

Quick reference

• 00:03 In this video, we're going to look at some basic statistics functions, and
• 00:07 you can see the functions we're going to examine are listed down here.
• 00:10 Count, CountA, Average, Max, Min, Median, and Mode.
• 00:15 These are the actual function names, and they all work slightly differently, and
• 00:18 I'm going to show you how these actually go through, and do their thing.
• 00:22 So, we're gong to start with equals count, and what I'm going do,
• 00:26 is I'm going to select this particular range of data here, and I'm
• 00:32 just going to press F4 a couple of times because I want the rows to stay absolute.
• 00:36 And I'm going to close the bracket, and then, press control.
• 00:40 Or rather command, Enter in order to commit that formula all the way across.
• 00:45 Now, what you'll notice is that in our account here,
• 00:49 it's actually counting the specific range.
• 00:52 And it's come up with a total of seven different items.
• 00:55 So why then, if we're looking at the next range over,
• 00:59 does it come up with 7, and the next one 6.
• 01:04 It's pointed at the correct area but
• 01:06 you'll notice that the cell that's yellow here is actually blank.
• 01:09 Now, if I were to put something in, this would change.
• 01:12 But a blank cell does not get counted, and
• 01:15 you'll also see as we work through the other functions,
• 01:17 that other functions don't actually end up picking it up either.
• 01:20 So, let's try this again with a count A.
• 01:23 And what the heck is the difference between count A, and count.
• 01:29 So again, we'll go F4 twice.
• 01:31 Close the bracket.
• 01:33 And we'll hit Command Enter to commit this to all the cells.
• 01:37 So, there is nothing different here at all between count and
• 01:40 count A, except when we do something like this.
• 01:43 Let's put an A in here.
• 01:45 Notice now ,that we actually end up having some differences.
• 01:49 The formula that is over here is trying to do subtraction with this particular item,
• 01:53 so it's throwing a value error.
• 01:55 A value error won't be counted using the count function.
• 01:59 But the count A function,
• 02:00 which is picking up that particular area, does count errors.
• 02:04 Count A also counts instances of text where count does not.
• 02:09 So, that's the difference there.
• 02:10 Count A is for working with text.
• 02:12 Neither of them however, count blanks, so
• 02:15 that's something interesting to be aware of.
• 02:18 What about average?
• 02:20 Let's try that.
• 02:21 =average.
• 02:23 We'll open our brackets.
• 02:24 Once again, we'll select this area,
• 02:26 press F4 twice to make it absolute for rows only.
• 02:31 Close our bracket, and hit Command+Enter to commit it to all the cells in here.
• 02:35 So, what it's done now is, if we actually go, and select this, you can see down
• 02:40 at the bottom my average is 38, and that's what it's actually picking up for me.
• 02:43 This is the mean average, so
• 02:45 what it does is it sums up all the numbers in the area that we've actually provided,
• 02:51 and then, it divides them by the total count of the numbers in the area as well.
• 02:54 And that's how it comes back with its number, and
• 02:56 you can test that out mathematically if you decide to do so.
• 02:59 What about the max function?
• 03:02 You would expect that this would probably give you the highest number
• 03:06 in any one of these ranges.
• 03:07 So, let's go, and try that out.
• 03:09 Write F4 a couple times again.
• 03:11 Close our brackets, command, enter and
• 03:14 as you can see the largest number in this set is 200, that's what it returns.
• 03:18 25 over here, that's what it's picking up.
• 03:21 And 50, as well, no problem.
• 03:24 So, max is working, what about min?
• 03:27 This could be kind of interesting, we'll see how it actually handles blank cells.
• 03:32 So, we'll go, and select this area again.
• 03:34 Press F4 a couple of times, close our brackets.
• 03:38 Once again, command enter, and interestingly enough, min ignores
• 03:45 the blank cell because you would think that this would be the lowest amount.
• 03:48 What happens again, and if we go, and put that to 0.
• 03:52 Well, at that point it's obviously,
• 03:54 gonna pick it up an show you that the minimum is the lowest number.
• 03:57 This now, evaluates us a number.
• 03:59 What about if we go with text?
• 04:01 It still tells me the minimum number is 2, but
• 04:04 obviously, I end up with some different issues in here.
• 04:07 I get a value error that shows up in this last column, so
• 04:10 it returns a value error to the end of the formula as well, okay.
• 04:13 So, we'll go Cmd+Z to just undo that, twice cuz I wanna go back to my blank.
• 04:18 Now, the Median gives us the middle value in the list of numbers,
• 04:23 and if they're sorted in ascending order.
• 04:26 So, let's go, and grab the median range here as well.
• 04:31 And again, we'll hit Command > Enter and
• 04:34 you can see that if you sorted all of these in numerical order
• 04:37 this would pick off the central value that it's in that sorted list.
• 04:40 Okay, so that's what's happening there.
• 04:43 And finally, we can go with Mode, and Mode gives us the most frequently
• 04:49 occurring number in a list as well, so just a slightly
• 04:54 different statistical average, if you like, that we can actually find.
• 04:59 So, in this particular case here,
• 05:01 we can see that 2 is the number that occurs most frequently in this column, and
• 05:05 13 is the number that occurs most frequently in this column as well.
• 05:10 So, those are the basic stats functions in Excel.

Lesson notes are only available for subscribers.