Payouts Commission

[exceldotcom]

Hi everyone!

Anyone know what kind of formula I could use to work out a score between tiers of achievement?

I.e. if 100% Payout Target = $50,000 the breakdown is as follows:

• 0% Achievement (A) Score = 0% Payout
• 35% A Score = 25% Payout
• 100% A Score = 100% Payout
• 140% A Score = 225% Payout
• The final calculation is for every 1% above 140% there will be a 3.75% payout based on the 100% payout target ($50k).

(There are many more here but really just need a little guidance on where to start, not asking anyone to do everything for me).

I basically need to work out the dollar value of the payout score for any % in between those Achievement breakpoint scores. Does this make sense - happy to add clarity if I have rambled!

Cheers!

 

[BiocideJ]

I believe this is what you are looking for...

=LOOKUP(A1,{0,0.35,1,1.4},{0,0.25,1,2.25})*A2+IF(A1>1.4,(A1-1.4)*3.75)*A2

where A1 is the Achievement Score (in percentage format)
and A2 is the Payout Target (e.g. $50,000)


EDIT: For clarity, I want to explain this portion
(A1-1.4)*3.75

really it is just a simplified (A1-1.4)*100*0.0375 (because if A1 is 150% for instance, the formula would evaluate to
=(1.5-1.4)*100*0.0375
= 0.1*100*.0375
= 10 * 0.0375 (because there are 10 1% increments
= .375 (37.5% additional bonus)

 

[exceldotcom]

Thank you, sir. I'm going to try and give this a go!

 

[exceldotcom]

Ok this seems to have worked - thanks again! I think the only question I have at the moment is instead of typing in percentage values into the formula. Would it be possible to change that to a cell reference (or cell range)? When I do this it seems to come up with an error.

Cheers!

 

[BiocideJ]

Surely. That's actually the typical way the LOOKUP formula would work.

Assuming you have your list of Achievement Tiers (0%, 35%, 100%, 140%, etc.) in G1:G4
And your associated Payouts in H1:H4
the amended formula would look like this.

=LOOKUP(A1,$G$1:$G$4,$H$1:$H$4)*A2+IF(A1>1.4,(A1-1.4)*3.75,0)*A2

EDIT: You can also put your bonus payout percentage (3.75%) in a formula, but just remember to multiply it by 100 for the reason I gave in my previous edit note.
EDIT TO THE EDIT:
Actually, just after posting the previous edit, I had an ever better idea to make this fully referenced.

=LOOKUP(A1,$G$1:$G$4,$H$1:$H$4)*A2+IF(A1>$G$4,(A1-$G$4)/$I$2*$I$1,0)*A2

Where I2 is the increment step (1%) over the max in G4 (140%) and I1 is the step bonus percentage (3.75%)

LAST EDIT (hopefully): I noticed I never put in my false portion of my IF statement.