CODEWITHSUNDEEP
excelmax
codingknob
codingknob

Reputation: 11660

max(row) but #N/A values in row

I am trying to determine the max of a row with 4 columns.

However, that row can have #N/A values.

I've read various posts on the web about functions like sumif() but there doesn't seem to be a maxif() function.

The following link describes how to do what I want to do using max(if()):

Maxif or Minif

However, when I try the following I get #VALUE as the result.

=MAX(IF(K4:N4<>"#N/A",K4:N4))

Cell K4 = 138
Cell L4 = FALSE
Cell M4 = #N/A
Cell N4 = #N/A

The result should be 138

Upvotes: 0

Views: 457

Answers (2)

user4039065
user4039065

Reputation:

See how to ignore errors with the AGGREGATE¹ function without CSE.

=aggregate(14, 6, K4:N4, 1)

¹ The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.

Upvotes: 1

sous2817
sous2817

Reputation: 3960

If the #N/A are the result of a formula error, you can try this:

=MAX(IF(NOT(ISERROR(K4:N4)),K4:N4))

This is an array formula and needs to be confirmed with Ctrl+Shift+Enter

Upvotes: 2

Related Questions