How to nest if and or not Excel

1
votes

I'm struggling with the following:

I have price ranges from

  • 100 - 200
  • 201 - 300
  • 301 - 400
  • 401 - 500
  • 501 - 600

In every range I give a number from 1-5. I'm trying to give a a number from 1 to 5 to a cell that will check in which price range is.

For example if I write in price field 150 it has to give me to the cell with the formula the number 1 according to the ranges I have. So far I've tried the following but I cannot nest more than 3 ifs.

=IF(AND(B9>=A10,B9<=C10),"1",(IF(AND(B9>=A11,B9<=C11),"2",IF(B9>=A12,B9<=C12,"3"))))
2
excelif-statement
If your price ranges are uniformly staggered as per your example, use =INT(B9/100). – user4039065
apparantly they are not.. Im looking something that will work with every numeric figure i give... i guess i was pretty clear. Thanks for your help though – Makis Kahrimanidis

2 Answers

2
votes

You could use =SUMPRODUCT() to do this. There are also some CSE formulas that would do the trick, but I prefer non-CSE since those can be finicky if someone messes with the formula and doesn't enter them properly:

=SUMPRODUCT((B9>=$A$10:$A$19)*(B9<=B10:B19)*(ROW($A$10:$A$19)-9))

Sumproduct will test each condition here that is then being multiplied together. The conditions in this formula are: (B9>=$A$10:$A$19) and (B9<=B10:B19) which are pretty self explanatory. From each condition, for each row in the range it gets a 1 or 0 (TRUE or FALSE) and then multiplies that by the ROW()-9 for each row being tested. In the end you get the ROW()-9 for whichever row has two TRUE conditions, or 1 * 1 * (Row()-9).

Note that because it tests each row, only one row should return two true conditions, otherwise you'll add up row numbers and get bad results.

0
votes

Set up a cross-reference table of the minimum amounts for each price range in ascending order. From your sample data this could be Z2:Z7.

        enter image description here

Your formula to examine the value entered into B9 would be,

=IFERROR(IF(B9<MAX($Z$2:$Z$7), MATCH(B9, $Z$2:$Z$7), NA()), "not in range")

If everything equal-to-and-above 501 should be in price group 5 then simply remove the top values (e.g. 601) and the check for the maximum.

=IFERROR(MATCH(B9, $Z$2:$Z$6), "not in range")

That will still return not in range for values less than 100 but anything over 500 will return price group 5.