If one of your cells in Excel has an error value then using the Autosum button to total or average the numbers in the cells will also result in an error.
I could get round this using the IFERROR function but another way to get round this is to use the AGGREGATE function.
This function has three main arguments, the function number, the options and the data range.
The function numbers are
- QUARTILE.EXCIn this case I want function 1 – AVERAGE. I am then given the choice of various options:
0. Ignore nested SUBTOTAL and AGGREGATE functions
- Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
- Ignore error values, nested SUBTOTAL and AGGREGATE functions
- Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
- Ignore nothing
- Ignore hidden rows
- Ignore error values
- Ignore hidden rows and error values
I am going to select number 6 – Ignore error values.
I then select the range that I want to average and type in a closing bracket. This time I have got an average of all amounts excluding the one giving me the error value.