Excel track · Exercise 77 of 86 Paid
AGGREGATE: a total that says what to ignore
Write a three-argument aggregate and know which cells the options number leaves out.
Advanced
Stock list where the total has to survive a broken row
Read-only data · screen 1 of 2 · answer in D5
In D5, add the prices up so that a broken cell in the range would not stop the total.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Qty | Value |
| 2 | pen | 10 | 2 | |
| 3 | pad | 5 | 4 | |
| 4 | ink | 20 | 3 | |
| 5 | Total stock |
In D5, average the prices with AGGREGATE and round the answer to one decimal place.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Qty | Value |
| 2 | pen | 10 | 2 | |
| 3 | pad | 5 | 4 | |
| 4 | ink | 20 | 3 | |
| 5 | Total stock |
Reference answer
What it does
AGGREGATE takes three arguments: a function number, an options number, then the range. The function number works as it does in SUBTOTAL, so 9 adds and 1 averages. The formula =AGGREGATE(9,6,B2:B4) adds the three prices to 35.
The options number decides what the calculation walks past. Six ignores error values, and seven ignores hidden rows as well. A plain SUM answers a broken reference with that same error across the whole column, while AGGREGATE keeps adding the cells that still hold numbers.
The cost of option six is that a failed row disappears from the total. Here B2:B4 is intact, so 35 is the plain sum as well. The second screen changes only the function number, to 1, and rounds 35 over 3 to 11.7.
Syntax
=AGGREGATE(function-number, options, range) with 9 for SUM, 1 for AVERAGE and 6 to skip error valuesWorked example
On Stock list where the total has to survive a broken row, =AGGREGATE(9,6,B2:B4) returns 35.
=AGGREGATE(9,6,B2:B4)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | The options number is not one AGGREGATE recognises, so it cannot decide what to skip. |
#NUM! | The function number is outside the range AGGREGATE supports. |
Wrong total | Option six has left a failed row out of the total, so the sum is one row short. |
At work
A total that keeps working when one row of the source data has broken.