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:
<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:
<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
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