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:

Units	$ MIN	$ MAX	Commission %
3	0	600,000	.50
8	600,001	1,600,000	.55
9	1,600,001		.60

<tbody>
</tbody>

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:

	N	O	P	Q
	Month	Units Sold	$ Sold	Commission I expect to pay
1	January	2	425000	.50
2	February	1	330000	.50
3	March	2	618000	.55
4	April	1	175000	.50
5	May	5	599000	.55
6	June	8	1500000	.55
7	July	7	1600010	.55
8	August	10	1500000	.60
9	September	9	1700000	.60
10	October	2	800000	.55

<tbody>
</tbody>

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:

Units	$ MIN	$ MAX	Commission %
3	0	600,000	.50
8	600,001	1,600,000	.55
9	1,600,001		.60

<tbody>
</tbody>

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.