RANK Function

[Terry Bennett]

I'm sure there is an easy way of doing this ...

I have a list of data values that I want to rank in terms of the
nearest/furthest away from zero. Some of the values are positive and some
are negative.

How do I use RANK such that No 1 is the value (positive or negative) that is
nearest to zero, No 2 is next nearest, etc?

Many thanks.

 

[Pete_UK]

You could use a helper column and put this formula in it:

=ABS(A1)

and copy this down - assume it is in column B. Then you could apply
the rank formula like this:

=RANK(B1,B$1:B$10,1)

and copy this down.

Hope this helps.

Pete

 

[T. Valko]

Try this...

Data in the range A1:A10

Enter this formula in B1 and copy down to B10:

=SUMPRODUCT(--(ABS(A1)>=ABS(A$1:A$10)))

 

[T. Valko]

Correction...

If you want to exactly emulate the RANK function:

=SUMPRODUCT(--(ABS(A1)>ABS(A$1:A$10)))+1

 

[Terry Bennett]

Many thanks Pete/Biff - that's what I needed!

Correction...

If you want to exactly emulate the RANK function:

=SUMPRODUCT(--(ABS(A1)>ABS(A$1:A$10)))+1

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Mike S.]

I have the very same issue. However, I have sub total between every 4 or 5
rows. so I would need the fomular to jump the total I may have 5 of them. If
or is that possible?
+2
+4
+3
total
+8
+6
+9
total

Please help...