Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank by flight
For the "rank within a flight" to work, I need to create two different formulas for row 1 to 4 and 5 to 8 separately. I was trying to create a fits-all formulat in the 4th column of the following spreadsheet. I tried using array function and it did not work. Please help. Rank Rank Flight Scores Full Field Flight A 78 1 1 A 92 6 4 A 90 4 3 A 88 2 2 B 91 5 2 B 98 7 3 B 89 3 1 B 100 8 4 Please help. Thanks. -- h317 ------------------------------------------------------------------------ h317's Profile: http://www.excelforum.com/member.php...o&userid=17043 View this thread: http://www.excelforum.com/showthread...hreadid=494871 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank by flight
h317,
For your example table, starting in cell A1: =1+SUMPRODUCT(($A$2:$A$9=A2)*($B$2:$B$9<B2)) HTH, Bernie MS Excel MVP "h317" wrote in message ... For the "rank within a flight" to work, I need to create two different formulas for row 1 to 4 and 5 to 8 separately. I was trying to create a fits-all formulat in the 4th column of the following spreadsheet. I tried using array function and it did not work. Please help. Rank Rank Flight Scores Full Field Flight A 78 1 1 A 92 6 4 A 90 4 3 A 88 2 2 B 91 5 2 B 98 7 3 B 89 3 1 B 100 8 4 Please help. Thanks. -- h317 ------------------------------------------------------------------------ h317's Profile: http://www.excelforum.com/member.php...o&userid=17043 View this thread: http://www.excelforum.com/showthread...hreadid=494871 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank by flight
Thanks, Bernie. It works great. Now I found myself in another situation: There are players who did not show up for games and the scorer assign a score of 0 to those players. When we do the ranking, those absent players, whose scores are 0, should be exclueded from the ranking. How can the formula that you created be changed to accocomodate for this? Thanks. -- h317 ------------------------------------------------------------------------ h317's Profile: http://www.excelforum.com/member.php...o&userid=17043 View this thread: http://www.excelforum.com/showthread...hreadid=494871 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank by flight
=IF(B2<0,1+SUMPRODUCT(($A$2:$A$9=A2)*($B$2:$B$9< 0)*($B$2:$B$9<B2)),"")
HTH, Bernie MS Excel MVP "h317" wrote in message ... Thanks, Bernie. It works great. Now I found myself in another situation: There are players who did not show up for games and the scorer assign a score of 0 to those players. When we do the ranking, those absent players, whose scores are 0, should be exclueded from the ranking. How can the formula that you created be changed to accocomodate for this? Thanks. -- h317 ------------------------------------------------------------------------ h317's Profile: http://www.excelforum.com/member.php...o&userid=17043 View this thread: http://www.excelforum.com/showthread...hreadid=494871 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rank where lowest value is highest rank | Excel Worksheet Functions | |||
Rank - Specific match only | Excel Worksheet Functions | |||
RANK formula | Excel Discussion (Misc queries) | |||
Rank fx - Fill Down? | Excel Worksheet Functions | |||
Rank Function | Excel Worksheet Functions |