Locked lesson.

Upgrade

About this lesson

Learn basic statistical functions including COUNT, COUNTA, AVERAGE, MAX, MIN, MEDIAN and MODE.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Basic Statistics.xlsx
15.8 KB Basic Statistics - Completed.xlsx
15.7 KB

Quick reference

Topic

Basic statistical functions including COUNT, COUNTA, AVERAGE, MAX, MIN, MEDIAN and MODE.

When to use

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.

Note: In addition to the ones covered here, there are many other statistical functions available in Excel.

Login to download
  • 00:04 In this video were going to look at basic statistics in Excel and how they work and
  • 00:08 differ between formulas.
  • 00:10 We're going to start with a basic count formula, so we'll say equals count, and
  • 00:15 we're going to select a set of cells, so
  • 00:19 go from C4 to C10, close our bracket and hit enter.
  • 00:22 And not surprisingly it counts seven
  • 00:25 values because there are seven cells that have values in them.
  • 00:29 If I go and
  • 00:29 extend this across though, you'll notice we get some different results.
  • 00:33 It counts seven values it counts seven.
  • 00:35 In this particular case it comes back with six and that's because.
  • 00:39 The count function does not count text where we see none.
  • 00:44 It also counts a six here.
  • 00:46 It doesn't count errors.
  • 00:48 So, #VALUE is an error.
  • 00:50 Those do not get counted even though there's actually seven
  • 00:53 items in these cells.
  • 00:54 So, something to be aware of.
  • 00:56 Let's look at Count A.
  • 00:58 So, Count A when we go and
  • 01:00 actually count will give us some slightly different results here.
  • 01:08 Notice that count A does count text.
  • 01:11 It also counts errors so that's something that's kind of interesting to know.
  • 01:16 I'm also going to show you now watch what happens to the numbers on the side
  • 01:20 when I delete this none and just leave it as blank.
  • 01:24 Notice that...
  • 01:25 Our count now counts seven values here because this is not an error anymore, but
  • 01:30 both columns ignore blanks altogether, so something to be aware of.
  • 01:36 Let's go with equals average.
  • 01:40 Average will look at the cells that we've provided.
  • 01:43 And we'll return the average value of those cells.
  • 01:47 And in the case of column E what the average is actually doing is it's
  • 01:52 summing all seven cells in a range and then dividing it by seven.
  • 01:56 In this particular case the average will actually sum all
  • 01:59 seven cells in the range but divide it by six because this cell is blank.
  • 02:03 So you can see the average is seven,
  • 02:05 the count is six, that matches with the formulas that are here.
  • 02:09 What about =max?
  • 02:12 If it's working the way you would expect, from this column we should pick up 125.
  • 02:16 Because that is the maximum value.
  • 02:18 And there it is.
  • 02:19 And as we scroll it across, you'll notice that we pick up 200, 25, and 50.
  • 02:23 All as the maximum values in that row.
  • 02:27 How about min?
  • 02:29 In this case we would expect that the minimum value for
  • 02:32 this particular column would be three, and that's exactly what the formula returns.
  • 02:38 Likewise, it returns seven for the next column over, but what will it return for
  • 02:42 column E?
  • 02:43 It actually returns two because blanks do not count in these kind of formulas.
  • 02:49 They will look at the lowest actual real value, not blank as a low value.
  • 02:54 So something to be aware of.
  • 02:56 Median is a different kind of average.
  • 02:59 If you're interested in those kind of things, I would highly suggest you look up
  • 03:02 the difference between mean, median, and mode on Wikipedia.
  • 03:07 But average returns the mean average, median is going to return the medium
  • 03:12 average, so in this case 25 and 40 from this one,
  • 03:18 three from this column which we don't actually even see that number in here, and
  • 03:22 13 from this one here, and then mode is yet another kind of average.
  • 03:26 A motor turns the most frequently occurring number
  • 03:30 I believe is the response for this one.
  • 03:33 So this would be a three.
  • 03:34 And as we scroll these across, we can see that 58 occurs twice and
  • 03:40 two occurs three times in here and 13 occurs three times.
  • 03:43 So that's the way those work.
  • 03:45 So basically you've got all kinds of regular stats formulas that you
  • 03:49 would expect to have from count, we have a separate function for
  • 03:52 counting Count A which is counting text values included.
  • 03:56 Average, that returns your mean average, your max and your minimum formulas.
  • 03:59 And your median and your mode to calculate your difference averages as well if you
  • 04:03 need those, so, lots of different variety for Excel in this particular area

Lesson notes are only available for subscribers.

Gift this course