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
--
Hennie


"T. Valko" wrote:

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%?

--
Biff
Microsoft Excel MVP


"Hennie" wrote in message
...
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
--
Hennie