Ranking Problem

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

Hi Jay,

I would try 2 helper columns:

Column 6 =if(isblank(4A),"",Column3A)

Column 7
=if(column2A>=1000,rank(column2A,Column2),rank(column6A,Column6)+countif(column2,">=1000"))

and copy down

André

"sa02000" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

=IF(ISNUMBER(D2),IF(B2>1000,SUMPRODUCT(--($B$2:$B$7>=B2)),SUMPRODUCT(--($B$2:$B$7<1000),--($C$2:$C$7>=C2))+SUMPRODUCT(--($B$2:$B$7>1000))),"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
>
>