IF/OR embedded function with more than two outcomes - doesn't work the way I've written it.

[Lizziepooh]

Excel for Mac 2011 Version 14.5.4

I've got a scenario where I'm trying to write an if/or function. Here's what I have so far: =IF(OR(C43=0,C44=0),0,IF(OR(C43<61,C44=1),0.05,IF(OR(C43>120,C44>4),0.15,0.1))).

The logic is: if C43 is between 1-60 or C44 =1, then the discount is 5%. If C43 is between 61-120 or C44 is between 2-4, then the discount is 10%. If C43 is greater than 120 or C44 is greater than 4, then the discount is 15%.

Someone suggested using AND instead of OR in the first function, which I've done so the first and second function work fine. Excel just ignores the third function though.

Can you help me fix this formula? Thank you!

 

[dreid1011]

Will C43 and C44 both have a value at the same time? Or will one of them always be blank?

 

[nicknep]

Here's what I came up with, assuming one of the cells will always be blank.

=IF(OR(AND(C43>=1,C43<=60),C44=1),0.05,IF(OR(AND(C43>=61,C43<=120),AND(C44>=2,C44<=4)),0.1,IF(OR(C43>120,C44>4),0.15,"")))

 

[Lizziepooh]

Yes, one of the two cells (C43 or C44) will always be blank. AND, more importantly, it works. Thank you so much! You have saved me a lot of time and hassle.

Here's what I came up with, assuming one of the cells will always be blank.

=IF(OR(AND(C43>=1,C43<=60),C44=1),0.05,IF(OR(AND(C43>=61,C43<=120),AND(C44>=2,C44<=4)),0.1,IF(OR(C43>120,C44>4),0.15,"")))