RANK formula question

[Tom Wright]

All,

I have worksheet that calculates rank based on a value in another column.
I am looking to use that rank to generate a point total in another column.
Here is a look at the columns

Name Points Rank
A 50
B 50
C 12
D 11

Rank could go from 0 to 50 let's say

Points are awarded by your rank position from 24 down to 1. The highest rank
will get 24 points, the next highest 23 points, etc. down to 1 point for a
non-zero result in rank (0 in rank = 0 points) . If there is a tie in rank,
then the points should be averaged among your rank positions. So in the
example above if A and B are tied for the highest rank they each would
receive (24+23)/2 = 23.5 points. If there are 24 or more non-zero values in
the rank column they will be assigned 1 point.

Make sense?

Any help is appreciated.

 

[Harlan Grove]

...
...

Points are awarded by your rank position from 24 down to 1. The highest rank
will get 24 points, the next highest 23 points, etc. down to 1 point for a
non-zero result in rank (0 in rank = 0 points) . If there is a tie in rank,
then the points should be averaged among your rank positions. So in the
example above if A and B are tied for the highest rank they each would
receive (24+23)/2 = 23.5 points. If there are 24 or more non-zero values in
the rank column they will be assigned 1 point.

Make sense?

Up until the last sentence your specs are clear enough. The last sentence is a
mystery. I'll guess you mean that if there were 25 or more positive ranks, then
the lowest positive ranks get 1 point each rather than fractional points. If so,
then if your ranks were in D251, try the following array formula for the
points that rank in D2 should receive.

=IF(COUNTIF(D$2$51,">"&D2)+COUNTIF(D$2$51,D2)<=24,
AVERAGE(25-COUNTIF(D$2$51,">"&D2)-ROW(INDIRECT("1:"&COUNTIF(D$2$51,D2)))),
--(COUNTIF(D$2$51,">"&D2)<24))

Select the cell containing this formula and fill down to match the range
containing the ranks. Note: the 25 just inside the AVERAGE call is intentional,
not a typo.

If all ties received the average of the remaining points for which they qualify,
then the sum of the points would always be 300 (the sum of 1..24). However, if
ties for the lowest positive rank produce more than 24 positive ranks overall,
the sum of points will vary around 300. The sum of points will be lower than 300
if the sum of remaining points exceeds the number of lowest positive ranks, and
higher than 300 if the sum of remaining points is less than the number of lowest
positive ranks.

For example, if there were 21 ranks above 1 and 4 ranks of 1, then the remaining
points would total 6 (=3+2+1) but each of these ranks would get 1 point, so the
total points overall would be 298. On the other hand, if there were 22 ranks
above 1 and 6 ranks of 1, then the remaining points would total 3 (=2+1) but
each of these ranks would get 1 point, so the total points overall would be 303.

 

[Tom Wright]

That is very close to what I am looking for...thank you. I tried your
formula and it mostly works. If I have 30 scores ranked as follows:

65 66 67 68 69 70 70 70 70 70 70 71 72 73 74 75 76 77 77 77 77 77 77 78 79
80 81 82 83 84

the 65 would get 24 pts, the 66 23 pts, etc. until you get to the ties at
70. They would each get (19 + 18 + 17 + 16 + 15 + 14)/6 or 16.5 points. At
the 77 score point they would get (7 + 6 + 5 + 4 + 3 + 2)/6 = 4.5 pts. Once
the first 23 scoring positions have been filled, everyone else would get one
point regardless of their ranking. If you haven't figured it out yet, this
is for a golf tornament and awarding points based on where one finishes
relative to everyone else.

Thanks for the help!

Tom

 

[Daniel.M]

Hi,

Assuming
Range C1:Cx contains your ranking (here C1:C30, adapt the formula for your
range),
A range called Pos contains the list of consecutive position (ascending, from 1
to 25 lets's say)
A range called Pts containing the points allowed to each position

In D1, the following ARRAY formula (Ctrl-Shift-Enter):
=MAX(1,AVERAGE(OFFSET(Pts,MATCH(C1,Pos)-1,0,COUNTIF($C$1:$C$30,C1),1)))

Copy to Dx.

Note: The Pos and Pts ranges are used to _decouple_ the Points allowed as a
result of the strict position (suppose you want to give 100 points to the 1st,
45 to the second, etc).

Regards,

Daniel M.

 

[Leo Heuser]

Tom

Assuming your numbers in B2:B?, this formula in e.g. C2 will do the job:

=MAX(24-RANK(B2,$B$2:$B$31,1)+1-(COUNTIF($B$2:$B$31,B2)-1)/2,1)

or

=MAX(25-RANK(B2,$B$2:$B$31,1)-(COUNTIF($B$2:$B$31,B2)-1)/2,1)

copy down with the fill handle.