100 points and descending

[gsdanger]

Good Day,
I need your assistance with this minor problem that is doin my head in.
I have a list of numbers (my race ratings).
Example:

86 100
84.5 ?
82 ?
79.5 ?
76 ?
74.5 ?
71 ?

I want to have the top rated figure to be 100, with the remaining ratings proportionally rated.
Naturally, the ratings will change with every use of my program, but I want the highest rated horse to have a rating of 100, and the rest allotted a rating that is proportional to the 100 top rating.
How can I do this? Please assist.

Kind regards.
gsdanger

 

[jarjarbingie]

good day,
I need your assistance with this minor problem that is doin my head in.
I have a list of numbers (my race ratings).
Example:

86 100
84.5 ?
82 ?
79.5 ?
76 ?
74.5 ?
71 ?

I want to have the top rated figure to be 100, with the remaining ratings proportionally rated.
Naturally, the ratings will change with every use of my program, but i want the highest rated horse to have a rating of 100, and the rest allotted a rating that is proportional to the 100 top rating.
How can i do this? Please assist.

Kind regards.
Gsdanger

=if($a2=max($a$2:$a$8),100,$a2/max($a$2:$a$8)*100)

 

[Peter_SSs]

A bit tricky without knowing the expected results or exactly what you mean by "proportionally rated". Here are 2 possibilities.
Column B is a simplified version of jarjarbingie's formula which effectively expresses the percentage of each score compared to the maximum score in the range.
Column C spreads the scores between 100 and zero, keeping the relative proportional gaps between them.

Book1
	A	B	C
2	86	100	100
3	84.5	98.2558	90
4	82	95.3488	73.333333
5	79.5	92.4419	56.666667
6	76	88.3721	33.333333
7	74.5	86.6279	23.333333
8	71	82.5581	0
Points

Cell Formulas
Range	Formula
B2	=100*A2/MAX(A$2:A$8)
C2	=100*(A2-MIN(A$2:A$8))/(MAX(A$2:A$8)-MIN(A$2:A$8))

 

[Swapnil Shah]

There is a third way to show rating in percentile Manner. Formula for the same is as below

=COUNTIF($A$2:$A$8,"<="&A2)/COUNTA($A$2:$A$8)*100

 

[gsdanger]

Good Day all,
Thanks to all that gave me a solution to this problem.
I used jarjarbingle's solution and it worked great....Thanks jarjarbingle, and thanks to Peter_SSs and Swapnil Shah.

Kind Regards.
gsdanger

 

[Peter_SSs]

You are welcome, and of course you can use whatever you like but just pointing out again that the considerably simpler formula in cell B2 of my post should produce exactly the same results.