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!

 

[steve the fish]

If im right the two points you know are 10% bonus = 101% achievement and 100% bonus = 200% achievement?


[TABLE="width: 395"]
<colgroup><col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;" span="2"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 90, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"]A[/TD]
[TD="width: 100, bgcolor: transparent"]B[/TD]
[TD="width: 90, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"]D[/TD]
[TD="width: 64, bgcolor: transparent"]E[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]Achievement[/TD]
[TD="bgcolor: transparent"]Bonus[/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"]101[/TD]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]150[/TD]
[TD="bgcolor: transparent"]54.55[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]200[/TD]
[TD="bgcolor: transparent"]100[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

D2 is your achievement to calc. E2 =

=IF(D2<101,0,MIN((D2-INTERCEPT(A2:A3,B2:B3))/SLOPE(A2:A3,B2:B3),100))

 

[adamas]

If im right the two points you know are 10% bonus = 101% achievement and 100% bonus = 200% achievement?


[TABLE="width: 395"]
<tbody>[TR]
[TD="width: 90, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"]A[/TD]
[TD="width: 100, bgcolor: transparent"]B[/TD]
[TD="width: 90, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"]D[/TD]
[TD="width: 64, bgcolor: transparent"]E[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]Achievement[/TD]
[TD="bgcolor: transparent"]Bonus[/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"]101[/TD]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]150[/TD]
[TD="bgcolor: transparent"]54.55[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]200[/TD]
[TD="bgcolor: transparent"]100[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

D2 is your achievement to calc. E2 =

=IF(D2<101,0,MIN((D2-INTERCEPT(A2:A3,B2:B3))/SLOPE(A2:A3,B2:B3),100))

Hey there, Steve! Thank you so very much for taking the time to reply, much much appreciate it!

Regarding your formula, I tried applying it to my task and hand and somehow it produces zeroes, so my guess is that I'm missing something here (or I badly explained where I need to get to and hence solution is for a different issue). Just to better illustrate my point, here's a simple sample file: https://www.dropbox.com/s/edwmvaqz5nie8nz/li-sample.xlsx?dl=0

All details should be there. I hope it's clear-enough now (and again - bad explanation is totally on me of course). Any chance you could have a look please?


Thanks so very, very much in advance!

 

[steve the fish]

This should work for you I believe:

In A3 and pull down:

=A2+(TargetMaximum-TargetMinimum)/(ROW(TargetMaximum)-2)

 

[adamas]

This should work for you I believe:

In A3 and pull down:

=A2+(TargetMaximum-TargetMinimum)/(ROW(TargetMaximum)-2)

Wow... And they still say Internet is only useful for exposed ladies and stuff... How wrong are those people..;|

But all my (flat) jokes aside, thank you so very much. I mean, seriously. I don't know how much time it took you, but you've just saved me literally hours of research (which I did already btw, with nothing much to show for this case of mine). You totally made my day and week, THANK YOU THANK YOU THANK YOU! : )))
/me tips his imaginary hat

 

[adamas]

This should work for you I believe:

In A3 and pull down:

=A2+(TargetMaximum-TargetMinimum)/(ROW(TargetMaximum)-2)

Hi there! A follow-up question to your answer, if I could.

Using the excellent answer you provided now I have a range of numbers, which change based on the maximum. As an illustration I've adjusted the sample a little bit: https://www.dropbox.com/s/ravr5wlzfco95xl/li-sample2.xlsx?dl=0


What I'm struggling with now is looking up a Bonus value for a given Target Achievement value, which is basically finding a matching point on a curve. If you have a look at the example file above, as an input I have a Target Achievement of 102% let's say. What would be a formula to return 10% for it? And so on.
To make it a bit more clearer, a lookup for Target Achievement of 101%, 102%, and 103% should return 10% Bonus. A lookup for 104, 105, 106 - should return 11%. Looking for 107 or 108 - returns 12%. And so on.


So what I'm dealing with is that I have a number of sales representatitves, for each I have a target achievement percentage. And then I need to match that Target Achievement (TA) value to a Bonus value. And TA goes linearly from a minimum of 101% to a maximum (which changes based on highest value among the sales people). So I need a formular which would retun a Bonus value based on TA value on that "line".


Ideally, I would love to do this without an intermediary table (one in that example is exactly that), because since maximum changes each time period - that changes the distribution of values - I simply am not sure how to make an intermediary table that would be so dynamic. Hence if there would be a formula than can return Bonus value for a given TA (so a place on a curve) based on min/max TA and min/max Bonus - that would be totally amazing.
But that's only ideally, meaning I could live with an intermediary table as well of course.


Any advice or pointers? : )))

 

[steve the fish]

Like this?

=LOOKUP(103%,A:A,B:B)

Change the 103% to a cell reference or whatever percentage you require

 

[steve the fish]

This formula does what you require:

=IF(A1<101,0,MIN(FORECAST(FLOOR(A1-101,3)+101,{10,100},{101,200}),100))

where: A1 houses the achievement
the 101 is the base level of achievement
3 is the step ie 101,102,103 all have same result.
10,100 are the min and max bonuses.
101,200 are the min and max achievement levels
finally 100 is max bonus.

They can all be changed to cell references if required.

 

[adamas]

This formula does what you require:

=IF(A1<101,0,MIN(FORECAST(FLOOR(A1-101,3)+101,{10,100},{101,200}),100))

where: A1 houses the achievement
the 101 is the base level of achievement
3 is the step ie 101,102,103 all have same result.
10,100 are the min and max bonuses.
101,200 are the min and max achievement levels
finally 100 is max bonus.

They can all be changed to cell references if required.

Steve, thanks a million for the answer!

I tried applying this formula to my example (the li-example2.xlsx) and somehow I can't get it to work. I mean, I tried different combos and sometimes it gives me 10% for every cell, sometimes -126% and the like. Any chance you'd be so very kind to "apply" it to my second example file just to illustrate the point? Because I do feel like I'm missing something here. But basically my understanding is that this "draggable" formula should start at Row 3 (e.g. let's use Column F, cell F3) - which corresponds to the first Yellow TA cell (A3), and then goes down until Row 291 (F291). AND values from Column A should match values from Column F = both of them generate the same result, but your "new" formula does it without an intermediary table.

Second thing, the stepping of it ("3 is the step ie 101,102,103"). Stepping is a dynamic thing, as far as I understand. For instance, with the TA Maximum of 900%, it's 3, but try changing the TA Maximum (A292) to let's say 5 000% and see how the steps change. What I'm saying is that I'm not sure this can work with static step (e.g. 3). But maybe I'm wrong on this ofc, so please do feel free to correct me on that.


Thanks so very, very much in advance!!

 

[steve the fish]

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.