Creating multiple conditons to select the highest between two conditions

[guttal]

I'm working on a commission schedule that pays out depending on which is higher, units or dollars based on three tiers. I'm using an if(and statement which seems to be working until I get to the third tier then the logic falls apart. The tiers are setup like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Units[/TD]
[TD]$ MIN[/TD]
[TD]$ MAX[/TD]
[TD]Commission %[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[TD]600,000[/TD]
[TD].50[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]600,001[/TD]
[TD]1,600,000[/TD]
[TD].55[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]1,600,001[/TD]
[TD][/TD]
[TD].60[/TD]
[/TR]
</tbody>[/TABLE]

I want to pay the commission % based on the higher of the unit numbers or dollar amount whichever is higher. I would like the total commission to be paid expressed as a dollar amount. My Q column represents the resulting commission I expect to pay based on the larger of O or P.

My data is setup as follows:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Month[/TD]
[TD]Units Sold[/TD]
[TD]$ Sold[/TD]
[TD]Commission I expect to pay[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]January[/TD]
[TD]2[/TD]
[TD]425000[/TD]
[TD].50[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]February[/TD]
[TD]1[/TD]
[TD]330000[/TD]
[TD].50[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]March[/TD]
[TD]2[/TD]
[TD]618000[/TD]
[TD].55[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]April[/TD]
[TD]1[/TD]
[TD]175000[/TD]
[TD].50[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]May[/TD]
[TD]5[/TD]
[TD]599000[/TD]
[TD].55[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]June[/TD]
[TD]8[/TD]
[TD]1500000[/TD]
[TD].55[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]July[/TD]
[TD]7[/TD]
[TD]1600010[/TD]
[TD].55[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]August[/TD]
[TD]10[/TD]
[TD]1500000[/TD]
[TD].60[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]September[/TD]
[TD]9[/TD]
[TD]1700000[/TD]
[TD].60[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]October[/TD]
[TD]2[/TD]
[TD]800000[/TD]
[TD].55[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The formula I created is:

=IF(AND($O$8<=3,$P$8<=600000),($P$8*0.005),IF(AND($O$8>3,$O$8<=8),($P$8*0.0055),IF(AND($P$8>=600001,$P$8<1600000),($P$8*0.0055),IF($O$8>=9,$P$8*0.006)*IF($P$8>=1600001,$P$8*0.006))))

Am I using the wrong formula for what I want to do? Any suggestions on how I can create the logic to make this work is appreciated.

Thank you

 

[Aroukouth]

(assuming your data starts in row 2) try this

=IF(OR(O2>=9,P2>=1600000),P2*0.006,IF(OR(O2>3,P2>600000),P2*0.0055,P2*0.005))

In your example, is the commission for July computed correctly? Sold = 1.600.010 (exceeding 1.600.000) which should result in a commission of .006 instead of .0055 if I interpreted your question correctly...

 

[guttal]

Thank you, you are correct. July should be .006. Thank you for the formula. I'm not sure I'm following the formula. I have to apply it to my spreadsheet to see each condition in action.

Thanks again.

 

[Aroukouth]

I can explain if you want:

The tiers are setup like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Units[/TD]
[TD]$ MIN[/TD]
[TD]$ MAX[/TD]
[TD]Commission %[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[TD]600,000[/TD]
[TD].50[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]600,001[/TD]
[TD]1,600,000[/TD]
[TD].55[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]1,600,001[/TD]
[TD][/TD]
[TD].60[/TD]
[/TR]
</tbody>[/TABLE]

I want to pay the commission % based on the higher of the unit numbers or dollar amount whichever is higher.

So, you want to pay the highest amount based on either units or $-value. Instead of excluding the lower commission values,

• I first check whether you have to pay the highest commission (which is the case if either the number of units >= 9 or the $-value exceeds 1.600.000) -> first IF, if this is true you pay 0.006 commission
• if you do not have to pay 0.006 commission we know that units <= 8 AND $-value <= 1.600.000, so I now check if you have to pay 0.0055 commission. Bearing in mind that units <= 8 AND $-value <= 1.600.000, I only have to check that units > 3 OR $-value > 600000 (second IF)
• If second if evaluates to false, we know that units <= 3 AND $-value <= 600.000, so we know that we have to pay 0.005 commission

 

[guttal]

I understand. There's no reason to step through each level if you can eliminate two levels the lowest level is the only option available.

Thank you for the explanation.