Ranking a list

[Bri]

Hi

I have a list of values that I need to rank in descending order. Usually
the RANK function is OK, but here is my problem. I need the list to use
'generous' rules. If there is a tie for 2nd, lets say, then I need the next
place to be 3rd, NOT 4th as the RANK function does.

eg
Scores Rank(correct) Rank(wrong)
14.04 1 1
12.665 2 2
12.665 2 2
11.725 3 4
0.000 4 5

Any ideas how I can deal with this situation?

Thanks in advance
Bri

 

[Domenic]

Assuming that A1:A5 contains your score...

B1, copied down:

=SUM(IF(A1<$A$1:$A$5,1/COUNTIF($A$1:$A$5,$A$1:$A$5)))+1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Peo Sjoblom]

Assuming the values are in A1:A5

=RANK(A1,$A$1:$A$5)-(COUNTIF($A$1:$A$5,">"&A1)-SUMPRODUCT((1/COUNTIF($A$1:$A$5,$A$1:$A$5))*($A$1:$A$5>A1)))


copy down

--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Bri]

Thank you, Domenic

There is a small concern left that I didn't mention in my original post.
The formula you gave is to be copied down a table that has 30 rows. Your
formula works perfectly, but it 'ties' all the blank cells for 1st, then
ranks the remaining cells properly as requested, starting at 2nd. I was
hoping to rank cells with values including 0.0000, but NOT RANK any cells
that are blank. Is there a fix?

bty, the exact same thing happens in the solution posed bu Peo.

Thankyou, Bri

 

[Bri]

Thank you, Peo

There is a small concern left that I didn't mention in my original post.
The formula you gave is to be copied down a table that has 30 rows. Your
formula works perfectly, but it 'ties' all the blank cells for 1st, then
ranks the remaining cells properly as requested, starting at 2nd. I was
hoping to rank cells with values including 0.0000, but NOT RANK any cells
that are blank. Is there a fix?

bty, the exact same thing happens in the solution posed bu Domenic.

Thankyou, Bri

 

[Domenic]

In that case, try the following formula instead...

=IF(A1<>"",SUM(IF($A$1:$A$5<>"",IF(A1<$A$1:$A$5,1/COUNTIF($A$1:$A$5,$A$1:
$A$5))))+1,"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Bob Phillips]

Just test it.

=IF(A2="","",RANK(A2,$A$2:$A$30)-(COUNTIF($A$2:$A$30,">"&A2)-SUMPRODUCT((1/C
OUNTIF($A$2:$A$30,$A$2:$A$30))*($A$2:$A$30>A2))))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Bri]

Domenic
This works perfectly! However, it prints FALSE in the blank cells that
don't get a ranking. Do you know of a way to suppress this? (Printouts of
the results will be distributed, so it would be better if the word FALSE
wasn't seen)

Thanks, Bri

 

[Domenic]

Actually, the formula shouldn't return FALSE. Make sure that you
include...

,"")

....at the end of the formula. Just to be sure, here's the formula
again...

=IF(A1<>"",SUM(IF($A$1:$A$5<>"",IF(A1<$A$1:$A$5,
1/COUNTIF($A$1:$A$5,$A$1:$A$5))))+1,"")

....which should be entered all on the same line.

 

[Bri]

Excellent!! You've helped a lot