How to rank

[jt114]

I am using excel 2003. I need to know how to rank the following?

results 10 -2 -5 25 31
ranking i would like 1 2 3 1 1
I want all results >0 to get a ranking of 1. and the ones that are <0 i
want stack ranked with the first result to get a ranking of 2. Can someone
help?

 

[T. Valko]

How should these numbers be ranked:

10...0...-2...-2...-3

-1...-1...-5...-5...-4

 

[jt114]

how they should be ranked

10 0 -2 -2 -3 -1 -1 -5 -5 -4
1 1 3 3 4 2 2 6 6 5

 

[T. Valko]

I'm not real sure about this but it works based on your sample numbers and
the results you say you want:

Numbers in the range A1:J1

Entered in A2 and copied across to J2:

=IF(A1>0,1,SUMPRODUCT(--($A1:$J1<=0),--(A1<$A1:$J1),1/COUNTIF($A1:$J1,$A1:$J1))+1)

 

[jt114]

worked on all but one. here are the numbers i am using

42.77 -13.55 33.24 65.61 27.83 -13.83 10.72 -29.75 -3.33
fomula 1 2 1 1 1 3 1
4 1

it is giving a rank of 1 to the -3.33 which should be a rank of 2

 

[T. Valko]

worked on all but one.

If -3.33 should be ranked 2 then all the other negative numbers rank needs
to increase by 1, right?

How is 0 ranked? Should it be ranked with the positive numbers or with the
negative numbers?

 

[jt114]

0 is ranked as 1. then starting with lowest negative number ranked as 2 and
so on until you rank the hightest negative number.

 

[T. Valko]

Ok, assuming no empty cells in the range:

=IF(A1>=0,1,SUMPRODUCT(--($A1:$I1<0),--(A1<$A1:$I1),1/COUNTIF($A1:$I1,$A1:$I1))+1+(COUNTIF($A1:$I1,">=0")>0))

 

[jt114]

perfect. thanks for all your help
--
John

Ok, assuming no empty cells in the range:

=IF(A1>=0,1,SUMPRODUCT(--($A1:$I1<0),--(A1<$A1:$I1),1/COUNTIF($A1:$I1,$A1:$I1))+1+(COUNTIF($A1:$I1,">=0")>0))

 

[T. Valko]

You're welcome. Thanks for the feedback!