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

 

[filipejrc]

Sorry I do not know how to edit my post, so I shall say that my formula above has an error, the range should be $C$1:$C$11 instead of $C$2:$C$12.

Best regards
Filipe Caetano

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F
1	1	Subtotal1	57.97	-100,25		Subtotals by Magnitude
2	2	Value1	10.25	-82,41		57.97
3	3	Value2	42.53	60,02		-54.38
4	4	Value3	5.19	57,97		-48.26
5	5	Subtotal2	-54.38	-54,38
6	6	Value1	45.87	-48,26
7	7	Value2	-100.25	45,87
8	8	Subtotal3	-48.26	42,53
9	9	Value1	-25.87	-25,87
10	10	Value2	-82.41	10,25
11	11	Value3	60.02	5,19
Sheet2

Cell Formulas
Range	Formula
F2	=IFERROR(INDEX(C:C,MOD(AGGREGATE(14,6,(ABS(C$1:C$100)*100000+ROW(B$1:B$100))/(LEFT($B$1:$B$100,8)="Subtotal"),ROWS(F$2:F2)),1000)),"")

This should work if you have only 2 decimal places, and have at most 999 rows of data.

 

[filipejrc]

Amazing job Eric!

Just what I needed.

Thank you very much once again for your precious help.

Best regards
Filipe Caetano

 

[Eric W]

You're welcome.