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.
<tbody>
</tbody>
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.
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.
| A | B | C | D | E | F | G | H | I | J | K | L | ||
| OFF1 | OFF2 | OFF3 | OFF4 | OFF5 | TOTAL SCORE | INTERVAL | SCORE OFF1 | SCORE OFF2 | SCORE OFF3 | SCORE OFF4 | SCORE OFF5 | ||
| 1 | CONCEPT A | 10 | 24 | 8 | 10 | 4 | 20 | 4 | 16 | 20 | 12 | 16 | 8 |
| 2 | CONCEPT B | 11 | 7 | 7 | 3 | 5 | 10 | 1 | 10 | 9 | 9 | 7 | 8 |
| 3 |
<tbody>
</tbody>
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.
