Linear Interpolation. From target, minimum, maximum, and step values

[adamas]

Hey there, guys!

I'm struggling with a certain issue a bit. I need to calculate monthly bonuses in Excel in an automated way. Which basically means I need to answer the following question: "If we know minimum and maximum bonus amounts, starting target achievement, and step value, how much the bonus value at each step should be?"

Here's what categories I'm working with:
- Target achievement minimum: starts with 101% (= for 100% achievement nothing is paid)
- Bonus minimum: starts with 10% of salary (= for achievement of 101% [above], one gets 10% bonus - so those are the starting points)
- Bonus maximum/cap/ceiling: ends at 100% (= for some maximum achievement of X, one gets 100% bonus - and that's the maximum possible/ceiling)
- Steps for target achievement: always 1% (meaning 101% achievement, 102%, 103%, 104%, etc.)

How do I "interpolate" this data in Excel and calculate the X (bonus that should be awarded) for each step of target achievement (starting from 102% - second point)? Meaning to calculate how much bonus would be for achievement of 102%, 103%, etc. BUT it all ends at the maximum bonus value/cap, which is whenever Bonus is maximum (= 100%). And it should go linearly (hence the linear in the topic).

The solution can be either in one formula that "looks" at target achievement and returns a corresponding bonus award amount (ideally), or via an intermediary data table (e.g. which would have target achievement values from 101% to X%, and corresponding bonus award amounts ending at 100% - and then vlookup will be made in that table).


I hope I was somehow clear on explaining what I need to get to. Any help/pointers would be much, much appreciated! And thanks a lot in advance guys!

 

[adamas]

That formula wasn't meant for your example. It was meant as a standalone formula because you asked for one without using the table. It can be adapted for use with the table if you require.

Also I thought you wanted steps? If not they can be removed so it is a true straight line relationship between achievement and bonus.

Steve,

I applied the formula to my example simply because it was listing TA values (to use column A's values to return the bonus). So I just wanted a set of TA values to "test" it, so to say. But I see your point, I do.

Regarding steps, I mentioned them to illustrate that bonus goes in 1% increments. Meaning nobody gets a 1.5% bonus, it's either 1% or 2%, etc.

Pfff... At this point I'm simply thinking I'm not explaining this correctly. I do apologise :/
Please let me give this another go. What I'm trying to do based on the following data:
- TA Min: 101% (stored in a cell, never changes)
- TA Max: X% (stored in a cell, changes every time period)
- Bonus Min: 10% (stored in a cell, never changes)
- Bonus Max: 300% (stored in a cell, never changes)
- Relationships between TA and Bonus is FULLY linear (increases in a linear fashion)

>>> WHAT IDEALLY I NEED: a formula that would, based on known info above, return a Bonus number for a given TA (that lies somewhere between minimum and maximim). So basically position on the line based on TA value


Again, I do apologise for the quality of my explanation... But I hope it's clear(er) now : ]

 

[steve the fish]

Create a table like below:


[TABLE="width: 282"]
<colgroup><col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="159" style="width: 119pt; mso-width-source: userset; mso-width-alt: 5814;"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 63, bgcolor: transparent"][/TD]
[TD="width: 159, bgcolor: transparent"]A[/TD]
[TD="width: 90, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Achievement[/TD]
[TD="bgcolor: transparent"]Bonus[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Min[/TD]
[TD="bgcolor: transparent"]101[/TD]
[TD="bgcolor: transparent"]10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]Max[/TD]
[TD="bgcolor: transparent"]200[/TD]
[TD="bgcolor: transparent"]200[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]Achievement[/TD]
[TD="bgcolor: transparent"]155[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]Bonus Absolute Value[/TD]
[TD="bgcolor: transparent"]113.6[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]Bonus Rounded Down[/TD]
[TD="bgcolor: transparent"]113[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8[/TD]
[TD="bgcolor: transparent"]Bonus Rounded Nearest[/TD]
[TD="bgcolor: transparent"]114[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Fill in your min and max values.
Fill in your actual achievement
Cell B6 contains: =IF(B5<$B$2,0,MIN(FORECAST(B5,$C$2:$C$3,$B$2:$B$3),$C$3))
Cell B7 contains: =ROUNDDOWN(B6,0)
Cell B8 contains: =ROUND(B6,0)

This will calculate your bonus.