Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking based on two columns
I need to rank a list based on two columns. So, first I have to rank all the data based on column1 if value is above certain no. but if cells have same values then rank those particular cells based on column2 and once the value in column 1 is below that threshhold rank based on column2 but if cells have same values then rank those particular cells based on column1. If column A value is greater than 1000 then First rank by columnA, if conflict in ranking (same value in column A) then rank by columnB, else rank by columnB, if conflict in ranking (same value in columnB) then rank by columnA again. Here is an example ColumnA....ColumnB.....Rank 4999....2.56......1 <<colA is greater than 1000, but rank based on colB 4999....1.59......2<<colA is greater than 1000, but rank based on colB 3149....3.59......3<<rank based on columnA 2482....0.00......4<<rank based on columnA 1712....0.00......5<<rank based on columnA 1422....0.73......6<<rank based on columnA 184......4.73......7<<colA smaller than1000, so rank based on colB 554......0.00......8<<colB has same values, rank using 4colA values only 377......0.00......9 298......0.00......10 196......0.00......11 and so on..... I tried this but obviously it gives me same rank number where column A and column B has same values... =IF(A2=1000,RANK(A2,$A$2:$A$42,0),RANK(B2,$B$2:$B $42,0)+COUNTIF($A$2:$A$42,"=1000")) from this I get rank as following... 1 1 3 4 5 6 7 8 8 8 8 I hope this is clear enough... Thanks, Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=532761 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking based on two columns
So, you basically have two lists - one with values in col A above 1000, the
other with values in col A below 1000 (what about those equal to 1000) Sort on column A & then physically separate the two lists. Sort each list independently, then recombine the lists "sa02000" wrote: I need to rank a list based on two columns. So, first I have to rank all the data based on column1 if value is above certain no. but if cells have same values then rank those particular cells based on column2 and once the value in column 1 is below that threshhold rank based on column2 but if cells have same values then rank those particular cells based on column1. If column A value is greater than 1000 then First rank by columnA, if conflict in ranking (same value in column A) then rank by columnB, else rank by columnB, if conflict in ranking (same value in columnB) then rank by columnA again. Here is an example ColumnA....ColumnB.....Rank 4999....2.56......1 <<colA is greater than 1000, but rank based on colB 4999....1.59......2<<colA is greater than 1000, but rank based on colB 3149....3.59......3<<rank based on columnA 2482....0.00......4<<rank based on columnA 1712....0.00......5<<rank based on columnA 1422....0.73......6<<rank based on columnA 184......4.73......7<<colA smaller than1000, so rank based on colB 554......0.00......8<<colB has same values, rank using 4colA values only 377......0.00......9 298......0.00......10 196......0.00......11 and so on..... I tried this but obviously it gives me same rank number where column A and column B has same values... =IF(A2=1000,RANK(A2,$A$2:$A$42,0),RANK(B2,$B$2:$B $42,0)+COUNTIF($A$2:$A$42,"=1000")) from this I get rank as following... 1 1 3 4 5 6 7 8 8 8 8 I hope this is clear enough... Thanks, Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=532761 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ranking based on two columns
Thanks Duke, Any Idea how to implement your logic..via macro or a function? Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=532761 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
get a total based on criteria in two columns | Excel Worksheet Functions | |||
how do I lookup data based on two columns of data | Excel Worksheet Functions | |||
Columns | Excel Discussion (Misc queries) | |||
How do I automatically hide columns in a worksheet based on a cell value? | Excel Worksheet Functions | |||
Hiding columns based on user/password | Excel Worksheet Functions |