CODEWITHSUNDEEP
excelexcel-formula
kristoff
kristoff

Reputation: 103

How to fix wrong output in IF ELSE formula?

Created a formula in which it rates an item. The problem is when a character or any invalid text, instead of number is entered, it still outputs "1" as the rating, when it should be ZERO as stated in the formula.

L7   | Rating Output
-0.5 | 5
A    | 1
100  | 1

=IF(L7<=-0.5,5,IF(AND(L7<=0,L7>-0.5),4,IF(AND(L7>0,L7<=0.25),3,IF(AND(L7>0.25,L7<=0.5),2,IF(L7>0.5,1,0)))))

How to fix? Can you see the error in my formula?

Thank you.

Upvotes: 0

Views: 38

Answers (2)

bosco_yip
bosco_yip

Reputation: 3802

1] Assume data put in L7:L9

2] In "rating output" M7. copied down :

=IFERROR(LOOKUP(L7,{-9.9E+307;-0.49;0.01;0.26;0.51},{5;4;3;2;1}),0)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

You could add another if-else level which first checks whether or not the input L7 be a number or not:

=IF(NOT(ISNUMBER(L7)), 0, IF(L7 <= -0.5, 5, IF(AND(L7 <= 0,L7 > -0.5), 4,
     IF(AND(L7 > 0, L7 <= 0.25) , 3, IF(AND(L7 > 0.25, L7 <= 0.5), 2, IF(L7 > 0.5, 1, 0))))))

Upvotes: 1

Related Questions