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...




"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Terry Bennett" wrote in message
...
Many thanks Pete/Biff - that's what I needed!


"T. Valko" wrote in message
...
Correction...

If you want to exactly emulate the RANK function:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)))

--
Biff
Microsoft Excel MVP


"Terry Bennett" wrote in message
...
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.









.