Rank and Scores

[rokew]

Hi,

I´m fighting against an elusive formula to assign scores, I have tried to use arrays and combinations of functions seen in old posts... with no positive results.

I have a long list of rows of concepts, each of them related to several values that appear in columns, so they must be compared horizontally (it´s an official form), they can be duplicated.

I must assign scores which vary according to the concept, the total of points and the interval. Repeated values receive the same score.

VALUES CONCEPT A: 10 , 24 , 8 , 10 , 4
Score to be assigned: The top value receives 20 points, the second 16, the third 12, the fourth 8Â… (that is, a maximum score of 20 and an interval of 4)

VALUES CONCEPT B: 11 , 7 , 7 , 3 , 5
Scores: The top value receives 10 points, the second, 9, the third 8, the fourth 7Â… (a maximum score of 10 and an interval of 1)

etc.

[TABLE="class: grid, width: 550, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]OFF1[/TD]
[TD]OFF2[/TD]
[TD]OFF3[/TD]
[TD]OFF4[/TD]
[TD]OFF5[/TD]
[TD]TOTAL
SCORE
[/TD]
[TD]INTERVAL
[/TD]
[TD]SCORE
OFF1
[/TD]
[TD]SCORE
OFF2
[/TD]
[TD]SCORE
OFF3
[/TD]
[TD]SCORE
OFF4
[/TD]
[TD]SCORE
OFF5
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]CONCEPT A[/TD]
[TD]10[/TD]
[TD]24[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]4[/TD]
[TD]20
[/TD]
[TD]4
[/TD]
[TD]16
[/TD]
[TD]20
[/TD]
[TD]12
[/TD]
[TD]16
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CONCEPT B[/TD]
[TD]11[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]10
[/TD]
[TD]1
[/TD]
[TD]10
[/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need a formula for columns H-L which detects the rank of values A-E, checks the total score (F), substracts the interval (G) and shows the final result in H-L.

Could you please help me?

I really appreciate any help you can provide.

 

[rokew]

Of course! I had already changed > but I was receiving an error message because I forgot to enter with CSE!

Thanks.

 

[Fluff]

You're welcome & thanks for the feedback