IIF with 2 conditions?

[Neilbees]

Hi

Hope someone can help with this. Had a search through the forums but nothing quite the same (although I'm guessing it's a simple one to solve.)

I need to calculate a figure based on 2 criteria. The permutations are as follows

If condition A is less than 20 and condition B is No then return 9
If condition A is less than 20 and condition B is No then return 12
If condition A is greater than 20 and condition B is No then return 13
If condition A is greater than 20 and condition B is No then return 15

Any suggestions?

 

[Rabbie]

I think you have a typo. First two conditions are identical but different value to be returned. Likewise the last two. I have assumed you want B=Yes for the conditions 2 & 4

iif(a<20 and B=No,9,iif(a<20 and b=yes,12,iif a>= 20 and B = No,13,15)))

should do the trick.

You also hadn't said what you wanted if A=20 so I assumed the same as >20.

 

[Moniker]

Rabbie is right in that your statements contradict each other. What you want is a Switch function. Going with Rabbie's B=Yes logic:

Switch(A<=20 And Not(B), 9, A<=20 And B, 12, A>20 And Not(B), 13, A>20 And B, 15)

 

[Neilbees]

Thanks guys, really appreciate the help. Problem solved even when I explained it wrong!