Find MAX value in data containing '#N/A'

[Steve_B]

Need a way to find the max value in data that may contain the error value '#N/A' instead of a numeric value. MAX() will return #N/A if it encounters it anywhere in the data being queried. It can't be that hard. Well, you would think not. Using Excel 2010.

[Pecoflyer]

Perhaps following array formula

=MAX(IF(NOT(ISNA($A$1:$A$50)),($A$1:$A$50)))

Commit with Ctrl+Shift+Enter

[Steve_B]

Thank you, Pecoflyer. Your solution is exactly what I needed. You are indeed an expert.

[Kevin@Radstock]

Originally Posted by Steve_B

Thank you, Pecoflyer. Your solution is exactly what I needed. You are indeed an expert.

If your using Excel 2010 as in your profile, why don't you use the AGGREGATE function (Non array) and assuming your data is in column A!

=AGGREGATE(4,6,A:A)