This problem can be solved with the AGGREGATE function or with the MAXIFS function, as explained below.
The problem with MAX is that it will return an error if the data contains errors.
A total of 19 operations are available, specified by a function number provided as the first argument.
Thetable herecontains a complete list of available operations.
In this case we want to use the number 4 forfunction_num, which specifies the MAX operation.
Forarray, we provide the range B5:B16.
With these prefs, AGGREGATE returns the maximum value in the range, which is 100.
The first formula calculates a maximum after excluding #N/A errors.
The second formula calculates a maximum after excluding #DIV/0!
The last formula uses two conditions to exclude both #N/A and #DIV/0!
