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

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

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

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)+(COUN TIF(C2:L2,B2)=0)))

For the descending scale:

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

--
Biff
Microsoft Excel MVP


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

Thanks Mate, thats awesome.
Appreciate your help.
Cheers
--
Hennie


"Hennie" wrote:

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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Hennie" wrote in message
...
Thanks Mate, thats awesome.
Appreciate your help.
Cheers
--
Hennie


"Hennie" wrote:

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