Search code examples
excelexcel-formulaexcel-2010

if statement not working properly on string data: 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))

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

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

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.

Solution

  • 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)