About this lesson
Learn basic statistical functions including COUNT, COUNTA, AVERAGE, MAX, MIN, MEDIAN and MODE.
Exercise files
Download this lesson’s related exercise files.
Basic Statistics.xlsx15.5 KB Basic Statistics - Completed.xlsx
15.9 KB
Quick reference
Topic
Basic statistics.
Description
Basic statistical functions including COUNT, COUNTA, AVERAGE, MAX, MIN, MEDIAN and MODE.
Where/when to use the technique
Determining basic statistics about the values that are in a range of data.
Instructions
COUNT vs COUNTA
=COUNT(A1:A10) |
Counts all numerical entries from A1 through A10. (Ignores text). |
=COUNTA(A1:A10) |
Counts all entries (including text) from A1 through A10. |
HIGH vs LOW values
=MAX(A1:A10) |
Returns the highest value in the provided range. |
=MIN(A1:A10) |
Returns the lowest value in the provided range. |
AVERAGES
=AVERAGE(A1:A10) |
Returns the mathematical average (mean) for the provided range. |
=MEDIAN(A1:A10) |
Returns the value of the number in the middle of your sorted data (or the value halfway between the two middle values if an even number of values is provided). |
=MODE(A1:A10) |
Returns the most frequently occurring value in your data set, or #N/A if no value occurs more frequently than any other. |
- 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.