Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() I have 4 columns of data with lots of rows (lets say 500). I would like to add 5th column ranking each row relative to other rows. But I need to check 3 different columns and depending on which column triggers, I need to add a rank. So for example, if numbers in column two are great than 1000 then the rank is decided based on column 2 but if column 2 values is less than 1000 then rank has to be decided based on values in column 3 but if column 4 has a certain value then I need to skip that row completely. Something along the lines below... I tried using if with Rank function..it skips the rows but it still increases the rank count by 1 Column1 Column2 Column3 Column4 Column5 (added) A 1243 .98 561 3 <--based on column2 B 1543 .89 561 2 <--based on column2 C 900 .45 561 5 <--based on column3 D 969 .56 561 4 <--based on column3 F 654 0.42 No list Skip <--based on column4 G 4532 0.56 561 1 <--based on column2 Any help will be very appriciated. Thank, Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=511197 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi Jay,
I would try 2 helper columns: Column 6 =if(isblank(4A),"",Column3A) Column 7 =if(column2A=1000,rank(column2A,Column2),rank(col umn6A,Column6)+countif(column2,"=1000")) and copy down André "sa02000" wrote in message ... I have 4 columns of data with lots of rows (lets say 500). I would like to add 5th column ranking each row relative to other rows. But I need to check 3 different columns and depending on which column triggers, I need to add a rank. So for example, if numbers in column two are great than 1000 then the rank is decided based on column 2 but if column 2 values is less than 1000 then rank has to be decided based on values in column 3 but if column 4 has a certain value then I need to skip that row completely. Something along the lines below... I tried using if with Rank function..it skips the rows but it still increases the rank count by 1 Column1 Column2 Column3 Column4 Column5 (added) A 1243 .98 561 3 <--based on column2 B 1543 .89 561 2 <--based on column2 C 900 .45 561 5 <--based on column3 D 969 .56 561 4 <--based on column3 F 654 0.42 No list Skip <--based on column4 G 4532 0.56 561 1 <--based on column2 Any help will be very appriciated. Thank, Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=511197 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
=IF(ISNUMBER(D2),IF(B21000,SUMPRODUCT(--($B$2:$B$7=B2)),SUMPRODUCT(--($B$2:$B$7<1000),--($C$2:$C$7=C2))+SUMPRODUCT(--($B$2:$B$71000))),"SKIP")
Copy this formula to E2, and then change the ranges to incorporate the hole list. Copy and paste the formula down the list. "sa02000" wrote: I have 4 columns of data with lots of rows (lets say 500). I would like to add 5th column ranking each row relative to other rows. But I need to check 3 different columns and depending on which column triggers, I need to add a rank. So for example, if numbers in column two are great than 1000 then the rank is decided based on column 2 but if column 2 values is less than 1000 then rank has to be decided based on values in column 3 but if column 4 has a certain value then I need to skip that row completely. Something along the lines below... I tried using if with Rank function..it skips the rows but it still increases the rank count by 1 Column1 Column2 Column3 Column4 Column5 (added) A 1243 .98 561 3 <--based on column2 B 1543 .89 561 2 <--based on column2 C 900 .45 561 5 <--based on column3 D 969 .56 561 4 <--based on column3 F 654 0.42 No list Skip <--based on column4 G 4532 0.56 561 1 <--based on column2 Any help will be very appriciated. Thank, Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=511197 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Problem With Reference Update | Excel Worksheet Functions | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
problem office assistant | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) | |||
Problem with ranking numbers | Excel Worksheet Functions |