if-then语句有多个选项

I have been working on an IF statement where there are multiple answers but I can't seem to get the formula to work. The If statement is going to look at two columns of data and should pull back the first NUMBER. Sometimes the first column will say "#N/A N/A" in which case the formula should look to the second column and pull that NUMBER. If both columns say "#N/A N/A" then I would like the answer to be "NR".

我一直在研究IF语句,其中有多个答案,但我似乎无法使公式起作用。 If语句将查看两列数据,并应该回退第一个NUMBER。有时第一列会说“#N / A N / A”,在这种情况下,公式应该查看第二列并拉出该NUMBER。如果两列都说“#N / A N / A”,那么我希望答案是“NR”。

In another scenario I will have three columns of data. I would like the formula to pick up the first number in the columns no matter what follows. Again, if all three columns say "#N/A N/A" I would like the answer to be "NR".

在另一种情况下,我将有三列数据。无论如何,我希望公式能够获取列中的第一个数字。同样,如果所有三列都说“#N / A N / A”,我希望答案是“NR”。

I will attach two examples of what I am talking about. Here is the formula I have now but I can't seem to get it to work:

我将附上两个我正在谈论的例子。这是我现在的公式,但我似乎无法让它工作:

=IF(ISBLANK(B3),"",IF(D3="#N/A N/A",$D3,$D4))

Unfortunately, this formula isn't taking into account if D3 is actually a number. It also doesn't account for if both D3 and D4 are "#N/A N/A".

不幸的是,如果D3实际上是一个数字,这个公式没有考虑到。它也没有考虑D3和D4是否都是“#N / A N / A”。

Any help would be greatly appreciated.

任何帮助将不胜感激。

Thanks,

Here is the example :

这是一个例子:

2 个解决方案

#1

This allows the use of many columns without the need for multiple nested ifs.

这允许使用许多列而无需多个嵌套ifs。

It will return the first column that is not #N/A N/A:

它将返回第一列不是#N / A N / A:

=IFERROR(INDEX(3:3,,AGGREGATE(15,6,COLUMN(D3:E3)/(D3:E3<>"#N/A N/A"),1)),"NR")

So to do more columns simple change both the D3:E3 to the range desired. The 3:3 should match the row being searched.

因此,要做更多列,只需将D3:E3更改为所需的范围。 3:3应与搜索的行匹配。

#2

You can check each cell (from left to right) and return the first instance of a numerical value using a combination of IF & ISNUMBER

您可以检查每个单元格(从左到右)并使用IF和ISNUMBER的组合返回数值的第一个实例

---

Checking 2 Columns

=IF(ISNUMBER(D3),D3,IF(ISNUMBER(E3),E3,"NR"))

---

Checking 3 Columns

=IF(ISNUMBER(D3),D3,IF(ISNUMBER(E3),E3,IF(ISNUMBER(F3),F3,"NR")))