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:

% of Salary Weight 0.20% 0.30% 0.40% Delinquent Loans 5% 1.15% 1.00% .85% <colgroup><col><col><col><col><col></colgroup><tbody> </tbody> 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).

<colgroup><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

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.