最大（行）但行中的#N / A值

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

我试图确定一行有4列的最大值。

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

但是,该行可以具有#N / A值。

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

我已经在网上阅读了关于sumif()等函数的各种帖子,但似乎没有maxif()函数。

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

以下链接描述了如何使用max(if())执行我想要执行的操作:

Maxif or Minif

Maxif或Minif

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

但是,当我尝试以下操作时,我得到#VALUE作为结果。

=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

结果应该是138

2 个解决方案

#1

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

如果#N / A是公式错误的结果,您可以尝试这样做:

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

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

这是一个数组公式,需要使用Ctrl + Shift + Enter确认

#2

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

了解如何在没有CSE的情况下忽略AGGREGATE¹函数的错误。

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

---

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

¹AGGREGATE函数是在Excel 2010中引入的。它在早期版本中不可用。