IF FUNCTION with multiple conditions

[abro16]

If number is 1 to 4 show "Scene 4"
If number is 5 to 9 show "Scene 3"
If number is 10 to 24 show "Scene 2"
If number is 25 to 35 show "Scene 1"
And if number is 0 or above 35 show "Invalid"

Please help.

 

[iliace]

You can use VLOOKUP for this. In my example, I created a separate lookup table in A1:B6. Then I use the approximate matching of VLOOKUP to get the result.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">-9999999</td><td >Invalid</td><td > </td><td style="text-align:right; ">0</td><td >Invalid</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td >Scene 4</td><td > </td><td style="text-align:right; ">1</td><td >Scene 4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">5</td><td >Scene 3</td><td > </td><td style="text-align:right; ">2</td><td >Scene 4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">10</td><td >Scene 2</td><td > </td><td style="text-align:right; ">3</td><td >Scene 4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">25</td><td >Scene 1</td><td > </td><td style="text-align:right; ">4</td><td >Scene 4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">36</td><td >Invalid</td><td > </td><td style="text-align:right; ">5</td><td >Scene 3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td style="text-align:right; ">9</td><td >Scene 3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td style="text-align:right; ">10</td><td >Scene 2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td style="text-align:right; ">24</td><td >Scene 2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td style="text-align:right; ">25</td><td >Scene 1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td style="text-align:right; ">34</td><td >Scene 1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td style="text-align:right; ">35</td><td >Scene 1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td style="text-align:right; ">36</td><td >Invalid</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >E1</td><td >=VLOOKUP(D1,$A$1:$B$6, 2, TRUE)</td></tr></table></td></tr></table>

 

[Skybluekid]

Welcome to the Board.

You could try:

=IF(OR((cell number)>=1,(cell number)<=4),"Scene 4",IF(OR((cell number)>=5,(cell number)<=9),"Scene 3",IF(OR((cell number)>=10,(cell number)<=24),"Scene 2",IF(OR((cell number)>=25,(cell number)<=35),"Scene 1","Invaild"))))

 

[iliace]

Welcome to the Board.

You could try:

=IF(OR((cell number)>=1,(cell number)<=4),"Scene 4",IF(OR((cell number)>=5,(cell number)<=9),"Scene 3",IF(OR((cell number)>=10,(cell number)<=24),"Scene 2",IF(OR((cell number)>=25,(cell number)<=35),"Scene 1","Invaild"))))

I think you're looking for AND instead of OR.

OR((cell number)>=1,(cell number)<=4)

if cell number is 5, will return true
if cell number is 0, will return true

OR is TRUE whenever either condition is met. AND is TRUE only when both conditions are met.

 

[Skybluekid]

I think you're looking for AND instead of OR.

OR((cell number)>=1,(cell number)<=4)

if cell number is 5, will return true
if cell number is 0, will return true

OR is TRUE whenever either condition is met. AND is TRUE only when both conditions are met.

You are right, I was half asleep when I wrote this!

 

[iliace]

You are right, I was half asleep when I wrote this!

There is validity to this approach, nonetheless. However, you probably want to take advantage of the finality of the true/false decision in an IF function. If a condition is evaluated as false, it cannot become true again. So, going by the original specs posted by abro, this is a good statement.

=IF(OR(A1<1, A1>35), "Invalid", IF(A1<5, "Scene 4", IF(A1<10, "Scene 3", IF(A1<25, "Scene 2", "Scene 1")))))

Since, at every nested IF statement, you check whether a certain condition is true, then if the formula moves on to the next condition, you know the previous condition must have been false. For example, if A1<5 is false for Scene 4, you know that A1 is >=5. There is no need to check for it again for Scene 3. At the end, since you already checked from A1>35 in the first statement, you know that if A1<25 is false, then A1 must be somewhere between 25 and 35.

It's all about how you structure the logic. The main thing I don't like about this approach is the nesting of IF statements - it's difficult to debug when you miss a parenthesis somewhere. The VLOOKUP approach eliminates the need for nested formulas, but also requires a separate table (or an array constant) to be used. There are advantages and drawbacks to either approach.