Return maximum number ignoring errors
This tutorial shows how to find the maximum number from a specified range ignoring errors using an Excel formula, with the AGGREGATE function
|
=AGGREGATE(4,6,B5:B11)
|
GENERIC FORMULA
=AGGREGATE(4,6,range)
ARGUMENTS EXPLANATION This formula uses the AGGREGATE function with the function_num criteria set to 4, which represents the MAX function, and the options criteria set to 6, which tells the function to ignore error values from the selected range.
Please note that this formula will ignore blank cells and will not account for them when identifying the maximum number.
In this example the range from which we are returning the highest number, ignoring errors, is B5:B11. The formula returns a value of 250, captured in cell B7, as this is the largest number in the selected range. |
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Average numbers and ignore zeros | How to average numbers in a range and ignore zeros | |
| Lookup nth largest value with criteria | How to lookup the nth largest value in a range with criteria | |
| Return largest number with criteria | How to get the largest number from a list with a specific criteria | |
| Find largest n numbers | How to find the n largest numbers from a specific range |