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.

		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.

 

[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
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	6	3	3	6	0	3	0, not -3

<thead>
</thead><tbody>
</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