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.

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		OFF1	OFF2	OFF3	OFF4	OFF5	TOTAL	INTERVAL	SCORE	SCORE	SCORE	SCORE	SCORE
2							SCORE		OFF1	OFF2	OFF3	OFF4	OFF5
3	CONCEPT A	10	24	8	10	4	20	4	16	20	12	16	8
4	CONCEPT B	11	7	7	3	5	10	1	10	9	9	7	8
Calendar

Cell Formulas
Range	Formula
I3	{=$G3-SUM(IF(B3<$B3:$F3,1/COUNTIF($B3:$F3,$B3:$F3)))*$H3}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[rokew]

Thanks a lot! It works like a charm but in some cases, due to the requirements of the evaluation, assigns a negative score which is not allowed.
For instance , for a total score of 6 points (decrease -2), the first value receives 6 points, the second 4, the third 2, the fourth 0 and the fifth -2.

How can the formula be adapted, please?

 

[Fluff]

Can you please supply a sample showing what you would want in that situation?

 

[rokew]

Negative values should be transformed into 0, like:

	A	B	C	D	E	F	G	H	I	J	K	L	M
OFF1	OFF2	OFF3	OFF4	OFF5	TOTAL	INTERVAL	SCORE	SCORE	SCORE	SCORE	SCORE
SCORE	OFF1	OFF2	OFF3	OFF4	OFF5
CONCEPT A

<thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]0
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]0, not -3
[/TD]

</tbody>

 

[Fluff]

Ok, use
=MAX($G3-SUM(IF(B3<$B3:$F3,1/COUNTIF($B3:$F3,$B3:$F3)))*$H3,0)
CSE entered.

 

[rokew]

Nailed it!
Thanks a lot.

 

[Fluff]

You're welcome & thanks for the feedback

 

[rokew]

Hi,

I’ve using the helpful formula discovered here:

=MAX($G3-SUM(IF(B3<$B3:$F3,1/COUNTIF($B3:$F3,$B3:$F3)))*$H3,0)


But in occasions I must use it inversely. Now the formula assigns the highest score to the highest value but sometimes I must assign the highest score to the lowest value. What changes must be made?

Thanks again!

 

[Fluff]

Simply change the < to a > like
=MAX($G3-SUM(IF(B3>$B3:$F3,1/COUNTIF($B3:$F3,$B3:$F3)))*$H3,0)
CSE entered