AGGREGATE for average ignoring errors
IntermediateWhen your data has errors scattered through a column, functions like AVERAGE fail on the whole range. AGGREGATE can skip those errors and give you the correct result.
In this exercise, you have monthly sales data with a Revenue per unit column that uses formulas referencing the units sold column. Two months show #DIV/0! because no units were sold, and one month shows #VALUE! because the data entry is still incomplete. A normal AVERAGE cannot handle these errors.
How AGGREGATE works
=AGGREGATE(function_num, options, range)
AGGREGATE takes three arguments:
- function_num: a number that tells AGGREGATE which calculation to run (1 for AVERAGE, 2 for COUNT, 9 for SUM, and more)
- options: a number that tells AGGREGATE what to ignore (6 means ignore error values)
- range: the cells to evaluate
For example, to sum while ignoring errors:
=AGGREGATE(9, 6, A1:A10)
Your task
The answer area starts in row 14. Complete all three cells in column B.
- In B14, write a regular
=AVERAGEformula for the Revenue per unit range so you can see what happens when AVERAGE encounters errors. - In B15, use AGGREGATE to calculate the correct average that skips the error values.
- In B16, use AGGREGATE again to count how many cells in the Revenue per unit column contain valid numbers.
When you're done, B14 should display an error, while B15 shows the true average and B16 shows the count of valid entries.
Need some help?
Hint 1
It is expected that B14 shows a #VALUE! error. That regular AVERAGE formula is there to show how AGGREGATE is different.
Hint 2
Check the function_num values listed in the instructions: which number corresponds to AVERAGE? Then use the right option to skip errors. The Revenue per unit column is the range.
Hint 3
For B16, use function_num 2 (COUNT) with the same options value of 6 to count only the non-error cells in the same range.
AGGREGATE for average ignoring errors
IntermediateWhen your data has errors scattered through a column, functions like AVERAGE fail on the whole range. AGGREGATE can skip those errors and give you the correct result.
In this exercise, you have monthly sales data with a Revenue per unit column that uses formulas referencing the units sold column. Two months show #DIV/0! because no units were sold, and one month shows #VALUE! because the data entry is still incomplete. A normal AVERAGE cannot handle these errors.
How AGGREGATE works
=AGGREGATE(function_num, options, range)
AGGREGATE takes three arguments:
- function_num: a number that tells AGGREGATE which calculation to run (1 for AVERAGE, 2 for COUNT, 9 for SUM, and more)
- options: a number that tells AGGREGATE what to ignore (6 means ignore error values)
- range: the cells to evaluate
For example, to sum while ignoring errors:
=AGGREGATE(9, 6, A1:A10)
Your task
The answer area starts in row 14. Complete all three cells in column B.
- In B14, write a regular
=AVERAGEformula for the Revenue per unit range so you can see what happens when AVERAGE encounters errors. - In B15, use AGGREGATE to calculate the correct average that skips the error values.
- In B16, use AGGREGATE again to count how many cells in the Revenue per unit column contain valid numbers.
When you're done, B14 should display an error, while B15 shows the true average and B16 shows the count of valid entries.
Need some help?
Hint 1
It is expected that B14 shows a #VALUE! error. That regular AVERAGE formula is there to show how AGGREGATE is different.
Hint 2
Check the function_num values listed in the instructions: which number corresponds to AVERAGE? Then use the right option to skip errors. The Revenue per unit column is the range.
Hint 3
For B16, use function_num 2 (COUNT) with the same options value of 6 to count only the non-error cells in the same range.