RANK ABSOLUTE NUMBER AND WITHOUT GAP
 

The value that I need to rank include positive and negative number. I need
to treat them all absolute value and also need to rank without gap.

VALUE RANK RESULT
-15 6
14 5
13 4
-12 3
-10 2
9 1
-9 1

Any ideas?
--
M&M

RANK ABSOLUTE NUMBER AND WITHOUT GAP
 

Hi,

If you pull the absolute value out to another column

=ABS(A2)

and drag down then you can use this formula

=SUMPRODUCT(--(A2$A$2:$A$8),1/COUNTIF($A$2:$A$8,$A$2:$A$8&""))+1

I'm sure it can be done without a helper column but I can't see it for the
moment.

Mike

"M&M" wrote:

The value that I need to rank include positive and negative number. I need
to treat them all absolute value and also need to rank without gap.

VALUE RANK RESULT
-15 6
14 5
13 4
-12 3
-10 2
9 1
-9 1

Any ideas?
--
M&M

RANK ABSOLUTE NUMBER AND WITHOUT GAP
 

Yes, I understand I can do it with the formula Mike suggested but I am trying
to find a solution without a helper column.
--
M&M


"Mike H" wrote:

Hi,

If you pull the absolute value out to another column

=ABS(A2)

and drag down then you can use this formula

=SUMPRODUCT(--(A2$A$2:$A$8),1/COUNTIF($A$2:$A$8,$A$2:$A$8&""))+1

I'm sure it can be done without a helper column but I can't see it for the
moment.

Mike

"M&M" wrote:

The value that I need to rank include positive and negative number. I need
to treat them all absolute value and also need to rank without gap.

VALUE RANK RESULT
-15 6
14 5
13 4
-12 3
-10 2
9 1
-9 1

Any ideas?
--
M&M

RANK ABSOLUTE NUMBER AND WITHOUT GAP
 

M&M <Missie.lucky.com wrote...
Yes, I understand I can do it with the formula Mike suggested but I am trying
to find a solution without a helper column.
....
"M&M" wrote:
....
VALUE *RANK RESULT
-15 * 6
14 * *5
13 * *4
-12 * 3
-10 * 2
9 * * 1
-9 * *1
....

If the VALUE column is sorted in descending absolute value, then if
the result table had -15 in cell A2, try

B2 [array formula]:
=SUM(1/MMULT(--(ABS(A2:A8)=TRANSPOSE(ABS(A2:A8))),ROW(A2:A8)^0))

B3:
=D2-(ABS(A3)<ABS(A2))

Fill B3 down as far as needed.

If the VALUE column were unsorted, it gets trickier. Try

B2 [array formula]:
=SUM((ABS(A$2:A$8)<=ABS(A2))/(MMULT(--(ABS(A$2:A$8)=TRANSPOSE(ABS(A$2:A
$8))),
--(ABS(A$2:A$8)<=ABS(A2)))+(ABS(A$2:A$8)ABS(A2))))

Fill B2 down as far as needed.