Calculate score from Scale

[Hennie]

Hi,
I have a spreadsheet (Excel 2007) with about 100 items. The below figures
represent a sample of the items.

I want to calculate the "Score" field by using the "Result" and calculate
the score between 1 - 10.

Example:
Item Result Scale
Score
Points 1 2 3 4 5 6 7
8 9 10
Item1 80% 55% 60% 65% 70% 75% 80% 85% 90% 95% 100% 6
Item2 2% 0% 1% 2% 3% 4% 5% 6% 7% 8% 9%
3
Item3 21 10 20 30 40 50 60 70 80
90 100 3

The formula should be able to calculate in reverse also as some score is
higher when the result is lower:

Item Result Scale
Score
Points 10 9 8 7 6 5 4
3 2 1
Item1 80% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% 3
Item2 2% 1% 2% 3% 4% 5% 6% 7% 8% 9% 10%
9
Item3 21 10 20 30 40 50 60 70 80
90 100 8

I've tried vlookup, but the items change on a monthly basis and I don't want
to redo the vlookup every month.

Can you please help.

Cheers

 

[T. Valko]

Item1,80%,10%,20%,30%,40%,50%,60%,70%,80%,90%,100%

In the above your lookup value is 80%. Will the lookup value ever be greater
than or less than the max value or the min value of the lookup array?

For example, will the lookup value ever be >100%?, Or, will the lookup value
ever be <10%?

 

[Hennie]

Thanks for your question.

Yes the values can go beyond the scale on both sides. ie <0% and >100%, but
it will then be either 0 or 10 on the scale.

Thanks

 

[T. Valko]

it will then be either 0 or 10 on the scale.

Ok, but your scale runs from 1 to 10 or 10 to 1.

For the ascending scale:

=IF(B2<=C2,C$1,IF(B2>=L2,L$1,MATCH(B2,C2:L2)+(COUNTIF(C2:L2,B2)=0)))

For the descending scale:

=IF(B2<=C2,C$1,IF(B2>=L2,L$1,INDEX(C$1:L$1,MATCH(B2,C2:L2)+(COUNTIF(C2:L2,B2)=0))))

 

[Hennie]

Thanks Mate, thats awesome.
Appreciate your help.
Cheers

 

[T. Valko]

You're welcome. Thanks for the feedback!