Rank into points

[Changbaishan]

Hello,

I just finished doing assessments for my 37 Athletes and now for my assessment report my team leader wants me to make each criteria worth the same amount of points as the percentage it represents. For example Jumps are worth 20% and I have 22 boys and 15 girls so I want to allocate a point value even to their rank and I was trying to use the Rank formula but not getting the results that I was hoping for. I want an even space between each point value and have to do his for all of the 9 criteria! I'm sure there is a fast way of doing this that calculating each rank individually.

Thank you for amy help you can provide.

Chang

 

[oldbrewer]

[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]RANKINGS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]student1[/TD]
[TD]student2[/TD]
[TD]student3[/TD]
[TD]student4[/TD]
[TD]student5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl22, align: right"]40%[/TD]
[TD]jumps[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl22, align: right"]60%[/TD]
[TD]hammer[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]as your post is unclear to me[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]pretend you have only 5 students[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]and only two disciplines - as above[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]in terms of this scenario, what do you want ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Changbaishan]

So here is an example of a criteria that is worth 10%:
[TABLE="width: 227"]
<colgroup><col width="67" style="width: 50pt;"><col width="80" span="2" style="width: 60pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 67, align: center"]Fei fei[/TD]
[TD="class: xl64, width: 80, align: right"]638[/TD]
[TD="class: xl65, width: 80, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Lily[/TD]
[TD="class: xl64, align: right"]614[/TD]
[TD="class: xl65, align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Lucy[/TD]
[TD="class: xl64, align: right"]607[/TD]
[TD="class: xl65, align: right"]13[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Landy[/TD]
[TD="class: xl64, align: right"]606[/TD]
[TD="class: xl65, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Eva[/TD]
[TD="class: xl64, align: right"]606[/TD]
[TD="class: xl65, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Vicki[/TD]
[TD="class: xl64, align: right"]604[/TD]
[TD="class: xl65, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Trawenya[/TD]
[TD="class: xl64, align: right"]602[/TD]
[TD="class: xl65, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Natalie[/TD]
[TD="class: xl64, align: right"]589[/TD]
[TD="class: xl65, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Yo yo[/TD]
[TD="class: xl64, align: right"]562[/TD]
[TD="class: xl65, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Ada[/TD]
[TD="class: xl64, align: right"]561[/TD]
[TD="class: xl65, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Xuan xuan[/TD]
[TD="class: xl64, align: right"]558[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Shirley[/TD]
[TD="class: xl64, align: right"]558[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Le le[/TD]
[TD="class: xl64, align: right"]543[/TD]
[TD="class: xl65, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]Qing[/TD]
[TD="class: xl64, align: right"]529[/TD]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66, align: center"]Co co[/TD]
[TD="class: xl67, align: right"]464[/TD]
[TD="class: xl68, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

So as you can see I just chose to give the person who did best the number of athletes and then all the way down to 1 for last place. Now I want to make the points rewarded to 1st being worth 10 and then all the way down last place and having the increment in-between be the same. Much the same as in the example above but into a figure up to the hundredth.Does that make more sense?

 

[oldbrewer]

if coco is given 10 then qing will be given 9.4 is that correct ?

 

[Fluff]

Is this what you're after

Excel 2013/2016
	A	B	C	D
2	Fei fei	638	15	10.00
3	Lily	614	14	9.33
4	Lucy	607	13	8.67
5	Landy	606	12	7.33
6	Eva	606	12	7.33
7	Vicki	604	10	6.67
8	Trawenya	602	9	6.00
9	Natalie	589	8	5.33
10	Yo yo	562	7	4.67
11	Ada	561	6	4.00
12	Xuan xuan	558	5	2.67
13	Shirley	558	5	2.67
14	Le le	543	3	2.00
15	Qing	529	2	1.33
16	Co co	464	1	0.67
Sheet2

Cell Formulas
Range	Formula
D2	=ROUND(RANK(B2,B2:B16,1)/ROWS(B2:B16)*10,2)

 

[oldbrewer]

Fluff - can you believe I ranked them in the wrong order....................

 

[Fluff]

Just realised that there is a problem with the formula, as it won't copy down.
It should be
=ROUND(RANK(B2,$B$2:$B$16,1)/ROWS($B$2:$B$16)*10,2)