CODEWITHSUNDEEP
excelformula
Steven
Steven

Reputation: 781

Compare percentage value against decimal Excel

I am a bit stumped with this issue, I was wondering if anyone could suggest a solution. In Excel I have a table which looks like this:

1   2   3   4   5   Result  Score
80% 85% 90% 95% 100%    92.5%   3.50

What I am trying to calculate is that proportional score, based on where the result falls within the preset decimal 1-5 score.

Thanks.

Upvotes: 0

Views: 654

Answers (2)

Aprillion
Aprillion

Reputation: 22304

for linear interpolation this would be general formula, just name the ranges or replace with cell references:

= (perc - minperc) / (maxperc - minperc) * (maxscore - minscore) + minscore

Upvotes: 0

barry houdini
barry houdini

Reputation: 46331

In your case where each increment is 5% you could use a simple calculation like

=MAX(0,F2-75%)*20

[where result is in F2]

....but assuming that you want to interpolate the score given potentially less linear values in your table try this formula where your table is in A1:E2

=LOOKUP(F2,A2:E2,A1:E1+(F2-A2:D2)*(B1:E1-A1:D1)/(B2:E2-A2:D2))

Upvotes: 3

Related Questions