Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranking based on two columns
I need to rank a list based on two columns. So, first I have to ran 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 (in column2) then rank thos particular cells based on column1. Here is how the logic flows.... 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 o 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 value 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:$ $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, Ja -- sa0200 ----------------------------------------------------------------------- sa02000's Profile: http://www.excelforum.com/member.php...fo&userid=2774 View this thread: http://www.excelforum.com/showthread.php?threadid=53340 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranking based on two columns
this worked with your 11 values in A2:A12
=MATCH(IF(A21000,A2,B2+0.000001*A2),LARGE(IF($A$2 :$A$121000,$A$2:$A$12,$B$2:$B$12+0.000001*$A$2:$A $12),{1,2,3,4,5,6,7,8,9,10,11}),0) Entered with Ctrl+Shift+Enter Rather than just enter in C2, then drag filled down to C11. -- Regards, Tom Ogilvy "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 (in column2) then rank those particular cells based on column1. Here is how the logic flows.... 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=533404 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranking based on two columns
Tom, Thanks for the reply... but I have 1000s of rows for which I need to do the ranking. Sorry I didn't say that explicitly in my original post. And this no. of rows changes from month to month.....so even if I put a big sequence for one month next month it will be off....any other ideas or can this be modified to do ranking for 1000s of rows?? Jay PS: I don't understand this formula and it didn't quite work for me.... :( -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=533404 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranking based on two columns
I created a dummy column with this formula =IF(E2=1000,RANK(E2,$E$2:$E$122,0)+RANK(I2,$I1:$I $122,0)/100,RANK(I2,$I$2:$I$122,0)+RANK(E2,$E$2:$E$122,0)/1000+COUNTIF($E$2:$E$122,"=1000")) and then ranked this dummy column to get my actual ranking =RANK(K2,$K$2:$K$122,1) I used help from this page by chris Pearson.... http://www.cpearson.com/excel/rank.htm Thanks for all those who replied to this and other similar posts. Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=533404 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranking based on two columns
I created a dummy column with this formula =IF(E2=1000,RANK(E2,$E$2:$E$122,0)+RANK(I2,$I1:$I $122,0)/100,RANK(I2,$I$2:$I$122,0)+RANK(E2,$E$2:$E$122,0)/1000+COUNTIF($E$2:$E$122,"=1000")) and then ranked this dummy column to get my actual ranking =RANK(K2,$K$2:$K$122,1) I used help from this page by chris Pearson.... http://www.cpearson.com/excel/rank.htm Thanks for all those who replied to this and other similar posts. Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=533404 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranking based on two columns
You can generate the sequence
with something like =row(1:1000) for example, in a new cell put in =row(1:100) then select row(1:100) and hit F9. then escape to return to the formula. If can be made dynamic. Put some data in A1:A15, then enter this formula elsewhe =row(indirect("1:" & counta(A1:A100))) Now select row(indirect("1:" & counta(A1:A100))) and hit F9. Actually, I was going to ask if you could use a dummy column - it is certainly easier - much simpler I think you will agree. -- Regards, Tom Ogilvy "sa02000" wrote: I created a dummy column with this formula =IF(E2=1000,RANK(E2,$E$2:$E$122,0)+RANK(I2,$I1:$I $122,0)/100,RANK(I2,$I$2:$I$122,0)+RANK(E2,$E$2:$E$122,0)/1000+COUNTIF($E$2:$E$122,"=1000")) and then ranked this dummy column to get my actual ranking =RANK(K2,$K$2:$K$122,1) I used help from this page by chris Pearson.... http://www.cpearson.com/excel/rank.htm Thanks for all those who replied to this and other similar posts. Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=533404 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranking based on two columns
Thanks for the reply Tom. I agree dummy column is certainly easier..... dummy (me) didn't even think about a dummy column..huh. I will try you solution also but may be not right away. Ja -- sa0200 ----------------------------------------------------------------------- sa02000's Profile: http://www.excelforum.com/member.php...fo&userid=2774 View this thread: http://www.excelforum.com/showthread.php?threadid=53340 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranking based on two columns
I created a dummy column with this formula =IF(E2=1000,RANK(E2,$E$2:$E$122,0)+RANK(I2,$I1:$I $122,0)/100,RANK(I2,$I$2:$I$122,0)+RANK(E2,$E$2:$E$122,0)/1000+COUNTIF($E$2:$E$122,"=1000")) and then ranked this dummy column to get my actual ranking =RANK(K2,$K$2:$K$122,1) I used help from this page by chris Pearson.... http://www.cpearson.com/excel/rank.htm Thanks for all those who replied to this and other similar posts. Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=533404 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranking based on sum across multiple locations | Excel Worksheet Functions | |||
Ranking based on conditions | Excel Worksheet Functions | |||
ranking based on criteria | Excel Worksheet Functions | |||
Ranking based on two columns | Excel Worksheet Functions | |||
Ranking based on frequency | Excel Worksheet Functions |