Lessons Lesson 34

AVERAGEIF

About this lesson

AVERAGEIF averages the cells in one range where the matching cell in another range meets a condition. It skips empty cells and divides only by the numbers it found.

By the end you can

  • average only the rows that match a condition.
  • say what AVERAGEIF returns when nothing matches.

Practises AVERAGEIF AVERAGEIFS SUMIF COUNTIF

By Ali, Founder, Excel Lessons

The idea

SUMIF's order, SUMIF's conditions, and the division at the end done for you. Empty cells in the averaged range are skipped, not counted as zero. So a journey with no time does not pull the average down.

The edge is a condition nothing matches. SUMIF and COUNTIF return 0 for that. AVERAGEIF returns #DIV/0!, because an average of no numbers is not a number. When the condition comes from a cell somebody can type anything into, that error will appear one day. The Errors module has the wrapper for it.

The mistake to watch for

Averaging the tested range instead of the one you meant. AVERAGEIF's third argument is what gets averaged. Leave it out and the condition column is averaged. For text that is an error. For numbers it is a wrong figure that looks right. And no matching rows is #DIV/0!, not 0. So wrap it where the condition can miss.

Read more about this

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

AVERAGEIF averages cells where a matching cell meets a condition.

In F2, type:

=AVERAGEIF(C2:C8,"Peak",D2:D8)

J7 has no logged time, so it's skipped.

F2
Row A B C D E F G
1 Journey Route Period Minutes
2 J1 12A Peak 44 Peak average
3 J2 12A Off-peak 31 Route 12A
4 J3 N7 Peak 52 36 minutes up
5 J4 N7 Off-peak 36 Named route
6 J5 12A Peak 48 Off-peak average
7 J6 N7 Off-peak 32
8 J7 12A Peak
9
10 Route N7

Every step

  1. 1

    AVERAGEIF averages cells where a matching cell meets a condition. In F2, type: =AVERAGEIF(C2:C8,"Peak",D2:D8). J7 has no logged time, so it's skipped.

    Hint. Test the periods for Peak, then average the minutes.

  2. 2

    Read =AVERAGEIF(B2:B8,"12A",D2:D8) and say what it returns for route 12A.

    Hint. Three timed 12A rows.

  3. 3

    Leave out the third argument when both ranges match. The condition can be a comparison. In F4, average journeys of 36+ minutes. Shape: =AVERAGEIF(range, test).

    Hint. ">=36".

  4. 4

    A condition can point at a cell instead of text. In F5, average the route named in B10. Remember: a cell reference needs no quotes.

    Hint. B10 as the criterion.

  5. 5

    In F6, average the off-peak journey times.

    Hint. Test the periods for Off-peak, then average the minutes.