ABS and LARGE Problem

[tljenkin]

Hi All,

Please help, I have a list that contains positive and negative numbers. Lets just say 10 cells containing positive and negative cells. I want to use large in an array to reorder the 10 cells, obviously highest to lowest but I want the re-ordered results to show negative signs for those items that were negative.

I used large and ABS and that worked in terms of reordering but all items are now positve. I dont know how to get excel to apply the negative sign to the original negative items.

Please help

Thanks

 

[XOR LX]

Hi,

Perhaps this array formula** in B1 and then copied down:

=INDEX($A$1:$A$10,MATCH(TRUE,ABS($A$1:$A$10)=LARGE(ABS($A$1:$A$10),ROWS($1:1)),0))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

[tljenkin]

Wow, I am speechless, thank you so much! i need to study this formula to understand the logic

 

[XOR LX]

You're welcome!

 

[pgc01]

Hi XOR LX

If you have the same absolute value twice, from one negative and one positive, you'll only catch the first one that appears in the range (twice). Not sure if it's important in this case, but maybe you want to tweak the formula?

 

[XOR LX]

Hi XOR LX

If you have the same absolute value twice, from one negative and one positive, you'll only catch the first one that appears in the range (twice). Not sure if it's important in this case, but maybe you want to tweak the formula?

A very good point. Thanks, PGC.

I guess one way (certainly not the most rigorous) would be to add a small increment to ensure uniqueness:

=INDEX($A$1:$A$10,MATCH(TRUE,ABS($A$1:$A$10)+1/(ROW($A$1:$A$10)*10^12)=LARGE(ABS($A$1:$A$10)+1/(ROW($A$1:$A$10)*10^12),ROWS($1:1)),0))

Imagine there's a better method, though. Will have a think.

Regards

 

[tljenkin]

This is just perfect! Thanks again both!

 

[filipejrc]

Hello.

Amazing formula!

How do I do this on a non contiguous range?

Thanks in advance.

Filipe Caetano

 

[Eric W]

Here's a formula (B1) that will work if the values are integers, and in the same column. The E1 formula is how to adapt it for non-contiguous cells. It still requires the values to be in the same column. Enter the row numbers of the cells you want to evaluate in the 2 array constants.

Book1
	A	B	C	D	E
1	1	-6		1	-6
2	2	5			5
3	-3	-4		2	-4
4	-1	-3			-3
5	2	-2		-3	-2
6	5	2			2
7	-6	2		-1	2
8	0	-1			-1
9	-4	1		2	1
10	-2	0			0
11				5
12
13				-6
14
15				0
16
17				-4
18
19				-2
Sheet2

Cell Formulas
Range	Formula
B1	=INDEX(A:A,MOD(AGGREGATE(14,6,ABS(A$1:A$10)*100+ROW(A$1:A$10),ROWS($B$1:$B1)),100))
E1	=INDEX(D:D,MOD(AGGREGATE(14,6,ABS(SUBTOTAL(9,OFFSET($D$1,{1,3,5,7,9,11,13,15,17,19}-1,0)))*100+{1,3,5,7,9,11,13,15,17,19},ROWS($E$1:$E1)),100))

If you have different requirements (non-integers, values not in same column), please let us know. Examples would help.

 

[filipejrc]

Thank you very much for replying.
I'll give you an example of what I need.

A	B	C	D
1	Subtotal1	57,97	-100,25
2	Value1	10,25	-82,41
3	Value2	42,53	60,02
4	Value3	5,19	57,97
5	Subtotal2	-54,38	-54,38
6	Value1	45,87	-48,26
7	Value2	-100,25	45,87
8	Subtotal3	-48,26	42,53
9	Value1	-25,87	-25,87
10	Value2	-82,41	10,25
11	Value3	60,02	5,19

<tbody>
</tbody>

D1	{=INDEX($C$2:$C$12,MATCH(LARGE(ABS($C$2:$C$12)+1/(ROW($C$2:$C$12)*10^12);A2),ABS($C$2:$C$12)+1/(ROW($C$2:$C$12)*10^12);FALSE);1)}

<tbody>
</tbody>

If I rank all numbers using a continuous range I get the results on column D, but I want to be able to do this just for Subtotal rows, so I'm looking for a way to rank only those subtotal values (non integer values).

I'm very sorry for any mistake I have made on the text, english is not my first language. Hope you understand what I need.

Thank you very much in advance.

Best regards
Filipe Caetano