Excel Practice
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

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.

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 the cells in one range where the matching cell in another meets a condition. It uses SUMIF's order: where to look, what to look for, what to average. The last journey, J7, ran but nobody logged a time.

In F2, type =AVERAGEIF(C2:C8,"Peak",D2:D8) for the average peak journey.

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 the cells in one range where the matching cell in another meets a condition. It uses SUMIF's order: where to look, what to look for, what to average. The last journey, J7, ran but nobody logged a time. In F2, type =AVERAGEIF(C2:C8,"Peak",D2:D8) for the average peak journey.

    Hint. Periods, "Peak", 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

    When the range tested is the range averaged, the third argument can go. The condition can be a comparison. In F4, average the journeys that took 36 minutes or more, on any route.

    Hint. ">=36".

  4. 4

    The route named in B10 is under review. In F5, average its journey times, pointing the condition at B10.

    Hint. B10 as the criterion.

  5. 5

    In F6, write a formula using AVERAGEIF that gives the average off-peak journey time.

    Hint. Periods, "Off-peak", minutes.