Find distance between 2 numbers and apply that distance to 2 other numbers

[silentdragonfly]

Hi all!
I have a model that helps me calculate a bonus based on hitting certain ratios. I'm having trouble writing a formula to do the calculation. Here's how one line looks:

[TABLE="width: 462"]
<colgroup><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 436"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]% of Salary Weight[/TD]
[TD="align: right"]0.20%[/TD]
[TD="align: right"]0.30%[/TD]
[TD="align: right"]0.40%[/TD]
[/TR]
[TR]
[TD]Delinquent Loans[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]1.15%[/TD]
[TD="align: right"]1.00%[/TD]
[TD="align: right"].85%[/TD]
[/TR]
</tbody>[/TABLE]

What this means is if our delinquency ratio is 1%, take the employee's salary, multiply it by 5% (the weight of this line item) and multiply that by 0.30 (the % of goal reached).

[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
This part is easy as long as the actual ratio is one of the 3 ratios listed (1.15%, 1.00%, or 0.85%). I'm trying to figure out how to extrapolate what the % of goal reached would be if the actual delinquency ratio is say 1.018% (ie where does that fall between 0.20% and 0.30%)?

Thanks!
Peg

 

[Gerald Higgins]

Looks like a maths question rather than an Excel question.

The maths might be something like this . . .
0.2+(0.3-0.2)x(1.15-1.018)/(1.15-1)
which gives an answer of
0.288

Is this the right answer ?

There are lots of ways of expressing this, some will be better than this.