Hi. I am trying to create a spreadsheet to calculate compensation. Since there are 5 different possible payouts I have been trying to combine the "IF","AND" and "OR" funcitions. My forumula looks like below, except I need to add 3 more layers and it is not working. I also provided a key for reference.

=IF(OR(AND(J6>=C9),J4*D9)=IF(AND(J6>=C8,J6
Key:
C8=90%
C9=100%
D8=$5
D9=$20
J4=number of sales
J6=J4/days worked in month



So if you exceed 100% of your goal the payout is $20*number of sales, and if you hit between 90% and 100% of your goal the payout is $15*number of sales.
What are the 5 possible payouts?

Where is the goal?


Solomon thanks for he reply. The goal is 60 sales per month. Hope this all makes sense. I changed some of the numbers around for simplicity.

Scenario 1: hit less than 50% of the goal and there is no BONUS
Scenario 2: hit between 50% and 60% of the goal, the payout is $20 x number of sales
Scenario 3: hit between 60% and 90% of the goal, the payout is $30 x number of sales
Scenario 4:hit been 90% and 100%, the payout is $40 x number of sales
Scenario 5: meet our exceed goal of 100%, the payout is $50 x number of sales

Key:
Column C is percentage of goal
C5=<50%
C6=50-60%
C7=60-80%
C8=90%
C9=100%

Column D is payout per sale
D5=$0
D6=$20
D7=$30
D8=$45
D9=$50


J4=number of sales
J6=% of goal met (or J4/60)
Which payout do the boundary VALUES get? (i.e. 60% 80% 100%) Are they the lower or higher? e.g. do 50% to 59% get $20 and 60% to 89% get $30 and 90% to 99% get $40 and 100% plus get $50?


Quote from: Salmon Trout on July 14, 2013, 12:49:44 PM

Which payout do the boundary values get? (i.e. 60% 80% 100%) Are they the lower or higher? e.g. do 50% to 59% get $20 and 60% to 89% get $30 and 90% to 99% get $40 and 100% plus get $50?