Ranking a list
 

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

Ranking a list
 

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!

In article ,
"Bri" wrote:

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

Ranking a list
 

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$5A1)))


copy down

--
Regards,

Peo Sjoblom

Portland, Oregon




"Bri" wrote in message
...
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

Ranking a list
 

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



"Domenic" wrote in message
...
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!

In article ,
"Bri" wrote:

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

Ranking a list
 

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

"Peo Sjoblom" wrote in message
...
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$5A1)))


copy down

--
Regards,

Peo Sjoblom

Portland, Oregon




"Bri" wrote in message
...
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

Ranking a list
 

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!

In article ,
"Bri" wrote:

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

Ranking a list
 

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$30A2))))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Bri" wrote in message
...
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

"Peo Sjoblom" wrote in message
...
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$5A1)))


copy down

--
Regards,

Peo Sjoblom

Portland, Oregon




"Bri" wrote in message
...
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

Ranking a list
 

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" wrote in message
...
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!

In article ,
"Bri" wrote:

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

Ranking a list
 

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.

In article ,
"Bri" wrote:

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

Ranking a list
 

Excellent!! You've helped a lot