S

#### sa02000

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