The review range has three readings and one #N/A. You need the largest usable number for a preliminary check, and the missing reading still needs attention. AGGREGATE can return the maximum while keeping the error in the source.

Use maximum mode and ignore errors

Put 12.4, #N/A, 15.1 and 13.8 in B2:B5 for the worked example. An example error can be made with =NA(). Enter:

=AGGREGATE(4,6,B2:B5)

Function number 4 selects MAX. Option 6 ignores error values. The result is 15.1.

Sample readings of 12.4, an error, 15.1 and 13.8 leave 15.1 as the largest numeric value.

Illustrated range. The error remains visible for investigation.

Report what the result covers

Verify that there are three numeric readings and that 15.1 is the largest. A label like “Highest available reading” makes the scope clear. Investigate the error separately. If there are no numeric readings, handle that as missing evidence before relying on a maximum.

An annotated AGGREGATE formula shows 4 for maximum and 6 for ignoring errors.

Illustrated formula proof. Compare the result with the numeric readings by hand.

Microsoft’s AGGREGATE reference.