About this lesson
AVERAGEIFS, MINIFS and MAXIFS: averages and extremes with conditions
AVERAGEIF, AVERAGEIFS, MINIFS and MAXIFS work like SUMIF and SUMIFS but return an average, smallest or largest value. =AVERAGEIF(B2:B13,"QLD",D2:D13) takes the range to average last, while =MAXIFS(E2:E13,D2:D13,">90") and AVERAGEIFS put it first, then range and condition pairs.
Using Google Sheets? Do this lesson in Sheets.
=AVERAGEIF(B2:B13,"QLD",D2:D13)What you'll practise
Test an audit client's trade debtors: average days outstanding by branch, the average overdue balance, the largest old balance and the oldest unpaid invoice.
4 steps · 40 points · the cells outlined in gold below
- AVERAGEIF
- AVERAGEIFS
- MAXIFS
- MINIFS
The data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Branch | Invoice date | Days out | Balance ($k) |
| 2 | Coastline Constructions | NSW | 19/05/2025 | 42 | 84 |
| 3 | Redgum Homes | QLD | 03/03/2025 | 119 | 212 |
| 4 | Parramatta Fitouts | NSW | 10/02/2025 | 140 | 47 |
| 5 | Moreton Bay Civil | QLD | 02/06/2025 | 28 | 315 |
| 6 | Yarra Joinery | VIC | 14/04/2025 | 77 | 63 |
| 7 | Hunter Valley Builders | NSW | 24/03/2025 | 98 | 129 |
| 8 | Sunshine Coast Decks | QLD | 20/01/2025 | 161 | 36 |
| 9 | Dandenong Steel Framing | VIC | 09/12/2024 | 203 | 274 |
| 10 | Illawarra Roofing | NSW | 16/06/2025 | 14 | 95 |
| 11 | Cairns Cabinetry | QLD | 28/04/2025 | 63 | 71 |
| 12 | Bendigo Renovations | VIC | 26/05/2025 | 35 | 118 |
| 13 | Wollongong Concrete | NSW | 25/11/2024 | 217 | 186 |
| 14 | |||||
| 15 | QLD average days out | ||||
| 16 | NSW over 60 days: avg ($k) | ||||
| 17 | Largest over 90 days ($k) | ||||
| 18 | Oldest VIC invoice |