CODEWITHSUNDEEP
excelif-statementexcel-formulaconditional-statements
user3460355
user3460355

Reputation: 5

Issue with excel if or statement?

From the following:

=IF(A2<100, "1", IF(OR(A2>=101, A2<=800), "2", IF(OR(A2>=801, A2<=1000), "3")))

when inputting a number, I can only get the outputs of 1 and 2. 3 will not be output if I input a number ranging from 801-1000. Anyone understand why?

Upvotes: 0

Views: 71

Answers (3)

barry houdini
barry houdini

Reputation: 46341

LOOKUP is another option, e.g. assuming A2 is a positive number

=LOOKUP(A2,{0,101,801,1001},{1,2,3,"Out of range"})

Upvotes: 0

Jerry
Jerry

Reputation: 71538

You can actually make the formula shorter as well, if you use something like this:

=IF(A2<100, "1", IF(A2<=800, "2", IF(A2<=1000, "3")))

That's assuming that there are no breaking points in the allowed range (re the issue with A2<=100 and if decimals are allowed (e.g. 800.5 would currently return FALSE with your formula, but will return 3 with the above one).

Or use a defined table for lookup purposes and then use VLOOKUP or INDEX/MATCH to get the corresponding values for the inserted values in cell A2 (this is particularly useful if you have a lot of different ranges for which there is to be different results).

Also note that in the above, you will be getting text values because of the quotes. If you need numeric values, remove them.

Upvotes: 1

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Use this one instead:

=IF(A2<100, "1", IF(AND(A2>=101, A2<=800), "2", IF(AND(A2>=801, A2<=1000), "3")))

also if A2 is 100 formula returns FALSE. Maybe you need to change A2<100 to A2<=100?

Upvotes: 2

Related Questions