if语句无法在字符串数据上正常运行:Excel [英] if statement not working properly on string data: Excel

问题描述

我在excel中有一张桌子

I have a table in excel

    E
1  Percent score
2   80%
3   N/A
4   100%

 =IF(E2>=0.90,1,IF(E2="N/A","N/A",0))

如此预期的输出(对于百分比> 90％的返回标志1，否则为0，对于N/A，返回N/A

So expected output (for percent >90% return flag 1, else 0, for N/A return N/A

         E                    F
1  Percent score             Overall flag
2   80%                        0
3   N/A                        N/A 
4   100%                        1

但是在不适用的情况下，使用此代码我也会得到1作为输出.怎么做

But in case of N/A also I am getting 1 as the output using this code. What to do

P.S.(在提出问题后进行编辑):N/A是此处的文本.这不是实际错误，而是纯文本.因此，如果在任何单元格中都有文本N/A，我希望将N/A作为输出返回.您能在这里帮忙吗?

P.S. (edit after question being asked): N/A is text here. It is not an actual error, it is plain text. So if we have text N/A in any cell, I would like N/A to be returned as output. Can you please help here.

推荐答案

根据我的评论，它看起来像"N/A"，是一个文本字符串，因此计算得出的值大于0.9，因此返回 1 .而是尝试:

As per my comment, it looks like "N/A" is a text-string and as such evaluates to be larger than 0.9 thus returning 1. Instead try:

=IF(E2="N/A",E2,--(E2>=0.9))

或更通用的解决方案也可以与其他文本值一起使用:

Or a more general solution to work with other text-values too:

=IFERROR(--(E2*1>=0.9),E2)

这篇关于if语句无法在字符串数据上正常运行:Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！