Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking a list
Excellent!! You've helped a lot
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update data validation list with new entries?? | Excel Discussion (Misc queries) | |||
List box setup, placement, and functionality | New Users to Excel | |||
Data Validation and Blanks in List | Excel Worksheet Functions | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Refresh a Validation List? | Excel Discussion (Misc queries) |