Calculating Bonus attainment %

[mashetterly]

I'm trying to write a formula that returns a % attainment based on a bonus achievement when referring to a target range.

The goal range is below from 0% to 200%. if the achievement was 265, the attainment% would be 100% (shown here). How do I write the formula so it calculates the exact % attainment for whatever "RESULT" I input? If the RESULT was 238, what would the Attainment % be?

<tbody>
[TD="class: xl66, align: center"] 0%
[/TD]
[TD="class: xl68, width: 81, align: center"] 90.0% [/TD]
[TD="class: xl66, width: 79, align: center"] 100% [/TD]
[TD="class: xl66, width: 97, align: center"] 200% [/TD]
[TD="class: xl69, width: 103, align: center"] RESULT [/TD]
[TD="width: 97, align: center"] Attainment % [/TD]

[TD="class: xl70, align: center"] 220.0 [/TD]
[TD="class: xl70, align: center"] 230.0[/TD]
[TD="class: xl70, align: center"] 265.0[/TD]
[TD="class: xl70, align: center"] 295.0[/TD]
[TD="class: xl71, align: center"] 265.0 [/TD]
[TD="class: xl72, align: center"]100%[/TD]

</tbody>

<tbody>
[TD="class: xl64, align: right"][/TD]
[TD="class: xl66, width: 81, align: right"][/TD]
[TD="class: xl64, width: 79, align: right"][/TD]
[TD="class: xl64, width: 97, align: right"][/TD]
[TD="class: xl67, width: 103"][/TD]
[TD="width: 97"][/TD]

[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl70, align: right"][/TD]

</tbody>

 

[Eric W]

Welcome to the forum.

That's a good question. What do you want it to be? Your ranges are not equal in size, neither the percentage gain, nor the achievement values. You're trying to do some interpolation, which basically means what you said, that given an x-value on a function between 2 other known x-values, what's the value of the function at that point? To do that, we need to figure out some way to approximate the function.

Possibly the easiest way would be to find out which range the value is in (220-230, 230-265, 265-295), then find a linear estimate of the value between those values. That would look something like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0%[/TD]
[TD]90.00%[/TD]
[TD]100%[/TD]
[TD]200%[/TD]
[TD]RESULT[/TD]
[TD]Attainment %[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]220[/TD]
[TD]230[/TD]
[TD]265[/TD]
[TD]295[/TD]
[TD]238[/TD]
[TD]92%[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=FORECAST(E2,OFFSET(A1,0,MATCH(E2,A2:D2)-1,1,2),OFFSET(A2,0,MATCH(E2,A2:D2)-1,1,2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]