software

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
Assuming that A1:A5 contains your score...

B1, copied down:

=SUM(IF(A1lt;$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 gt;,
quot;Briquot; gt; wrote:

gt; Hi
gt;
gt; I have a list of values that I need to rank in descending order. Usually
gt; the RANK function is OK, but here is my problem. I need the list to use
gt; 'generous' rules. If there is a tie for 2nd, lets say, then I need the next
gt; place to be 3rd, NOT 4th as the RANK function does.
gt;
gt; eg
gt; Scores Rank(correct) Rank(wrong)
gt; 14.04 1 1
gt; 12.665 2 2
gt; 12.665 2 2
gt; 11.725 3 4
gt; 0.000 4 5
gt;
gt; Any ideas how I can deal with this situation?
gt;
gt; Thanks in advance
gt; Bri

Assuming the values are in A1:A5

=RANK(A1,$A$1:$A$5)-(COUNTIF($A$1:$A$5,quot;gt;quot;amp;A1)-SUMPRODUCT((1/COUNTIF($A$1:$A$5,$A$1:$A$5))*($A$1:$A$5gt;A1)))copy down

--
Regards,

Peo Sjoblom

Portland, Oregon

quot;Briquot; gt; wrote in message
...
gt; Hi
gt;
gt; I have a list of values that I need to rank in descending order. Usually
gt; the RANK function is OK, but here is my problem. I need the list to use
gt; 'generous' rules. If there is a tie for 2nd, lets say, then I need the
gt; next place to be 3rd, NOT 4th as the RANK function does.
gt;
gt; eg
gt; Scores Rank(correct) Rank(wrong)
gt; 14.04 1 1
gt; 12.665 2 2
gt; 12.665 2 2
gt; 11.725 3 4
gt; 0.000 4 5
gt;
gt; Any ideas how I can deal with this situation?
gt;
gt; Thanks in advance
gt; Bri
gt;
gt;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
quot;Domenicquot; gt; wrote in message
...
gt; Assuming that A1:A5 contains your score...
gt;
gt; B1, copied down:
gt;
gt; =SUM(IF(A1lt;$A$1:$A$5,1/COUNTIF($A$1:$A$5,$A$1:$A$5))) 1
gt;
gt; ...confirmed with CONTROL SHIFT ENTER, not just ENTER.
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; quot;Briquot; gt; wrote:
gt;
gt;gt; Hi
gt;gt;
gt;gt; I have a list of values that I need to rank in descending order. Usually
gt;gt; the RANK function is OK, but here is my problem. I need the list to use
gt;gt; 'generous' rules. If there is a tie for 2nd, lets say, then I need the
gt;gt; next
gt;gt; place to be 3rd, NOT 4th as the RANK function does.
gt;gt;
gt;gt; eg
gt;gt; Scores Rank(correct) Rank(wrong)
gt;gt; 14.04 1 1
gt;gt; 12.665 2 2
gt;gt; 12.665 2 2
gt;gt; 11.725 3 4
gt;gt; 0.000 4 5
gt;gt;
gt;gt; Any ideas how I can deal with this situation?
gt;gt;
gt;gt; Thanks in advance
gt;gt; 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

quot;Peo Sjoblomquot; gt; wrote in message
...
gt; Assuming the values are in A1:A5
gt;
gt; =RANK(A1,$A$1:$A$5)-(COUNTIF($A$1:$A$5,quot;gt;quot;amp;A1)-SUMPRODUCT((1/COUNTIF($A$1:$A$5,$A$1:$A$5))*($A$1:$A$5gt;A1)))
gt;
gt;
gt; copy down
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Portland, Oregon
gt;
gt;
gt;
gt;
gt; quot;Briquot; gt; wrote in message
gt; ...
gt;gt; Hi
gt;gt;
gt;gt; I have a list of values that I need to rank in descending order. Usually
gt;gt; the RANK function is OK, but here is my problem. I need the list to use
gt;gt; 'generous' rules. If there is a tie for 2nd, lets say, then I need the
gt;gt; next place to be 3rd, NOT 4th as the RANK function does.
gt;gt;
gt;gt; eg
gt;gt; Scores Rank(correct) Rank(wrong)
gt;gt; 14.04 1 1
gt;gt; 12.665 2 2
gt;gt; 12.665 2 2
gt;gt; 11.725 3 4
gt;gt; 0.000 4 5
gt;gt;
gt;gt; Any ideas how I can deal with this situation?
gt;gt;
gt;gt; Thanks in advance
gt;gt; Bri
gt;gt;
gt;gt;
gt;
In that case, try the following formula instead...

=IF(A1lt;gt;quot;quot;,SUM(IF($A$1:$A$5lt;gt;quot;quot;,IF(A1lt;$A$1:$A$5,1/COUNTIF($A$1:$A$5,$A$1:
$A$5)))) 1,quot;quot;)

....confirmed with CONTROL SHIFT ENTER.

Hope this helps!

In article gt;,
quot;Briquot; gt; wrote:

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

Just test it.

=IF(A2=quot;quot;,quot;quot;,RANK(A2,$A$2:$A$30)-(COUNTIF($A$2:$A$30,quot;gt;quot;amp;A2)-SUMPRODUCT((1/C
OUNTIF($A$2:$A$30,$A$2:$A$30))*($A$2:$A$30gt;A2))))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Briquot; gt; wrote in message
...
gt; Thank you, Peo
gt;
gt; There is a small concern left that I didn't mention in my original post.
gt; The formula you gave is to be copied down a table that has 30 rows. Your
gt; formula works perfectly, but it 'ties' all the blank cells for 1st, then
gt; ranks the remaining cells properly as requested, starting at 2nd. I was
gt; hoping to rank cells with values including 0.0000, but NOT RANK any cells
gt; that are blank. Is there a fix?
gt;
gt; bty, the exact same thing happens in the solution posed bu Domenic.
gt;
gt; Thankyou, Bri
gt;
gt; quot;Peo Sjoblomquot; gt; wrote in message
gt; ...
gt; gt; Assuming the values are in A1:A5
gt; gt;
gt; gt;
=RANK(A1,$A$1:$A$5)-(COUNTIF($A$1:$A$5,quot;gt;quot;amp;A1)-SUMPRODUCT((1/COUNTIF($A$1:$A
$5,$A$1:$A$5))*($A$1:$A$5gt;A1)))
gt; gt;
gt; gt;
gt; gt; copy down
gt; gt;
gt; gt; --
gt; gt; Regards,
gt; gt;
gt; gt; Peo Sjoblom
gt; gt;
gt; gt; Portland, Oregon
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Briquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; Hi
gt; gt;gt;
gt; gt;gt; I have a list of values that I need to rank in descending order.
Usually
gt; gt;gt; the RANK function is OK, but here is my problem. I need the list to
use
gt; gt;gt; 'generous' rules. If there is a tie for 2nd, lets say, then I need the
gt; gt;gt; next place to be 3rd, NOT 4th as the RANK function does.
gt; gt;gt;
gt; gt;gt; eg
gt; gt;gt; Scores Rank(correct) Rank(wrong)
gt; gt;gt; 14.04 1 1
gt; gt;gt; 12.665 2 2
gt; gt;gt; 12.665 2 2
gt; gt;gt; 11.725 3 4
gt; gt;gt; 0.000 4 5
gt; gt;gt;
gt; gt;gt; Any ideas how I can deal with this situation?
gt; gt;gt;
gt; gt;gt; Thanks in advance
gt; gt;gt; Bri
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt;
gt;
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

quot;Domenicquot; gt; wrote in message
...
gt; In that case, try the following formula instead...
gt;
gt; =IF(A1lt;gt;quot;quot;,SUM(IF($A$1:$A$5lt;gt;quot;quot;,IF(A1lt;$A$1:$A$5,1/COUNTIF($A$1:$A$5,$A$1:
gt; $A$5)))) 1,quot;quot;)
gt;
gt; ...confirmed with CONTROL SHIFT ENTER.
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; quot;Briquot; gt; wrote:
gt;
gt;gt; Thank you, Domenic
gt;gt;
gt;gt; There is a small concern left that I didn't mention in my original post.
gt;gt; The formula you gave is to be copied down a table that has 30 rows. Your
gt;gt; formula works perfectly, but it 'ties' all the blank cells for 1st, then
gt;gt; ranks the remaining cells properly as requested, starting at 2nd. I was
gt;gt; hoping to rank cells with values including 0.0000, but NOT RANK any cells
gt;gt; that are blank. Is there a fix?
gt;gt;
gt;gt; bty, the exact same thing happens in the solution posed bu Peo.
gt;gt;
gt;gt; Thankyou, Bri
Actually, the formula shouldn't return FALSE. Make sure that you
include...

,quot;quot;)

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

=IF(A1lt;gt;quot;quot;,SUM(IF($A$1:$A$5lt;gt;quot;quot;,IF(A1lt;$A$1:$A$5,
1/COUNTIF($A$1:$A$5,$A$1:$A$5)))) 1,quot;quot;)

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

In article gt;,
quot;Briquot; gt; wrote:

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

Excellent!! You've helped a lot