# Thread: ABS and LARGE Problem Thanks: 0 Likes: 0

1. ## ABS and LARGE Problem

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

2. ## Re: ABS and LARGE Problem

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

3. ## Re: ABS and LARGE Problem

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

4. ## Re: ABS and LARGE Problem

You're welcome!

5. ## Re: ABS and LARGE Problem

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?

6. ## Re: ABS and LARGE Problem

Originally Posted by 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?
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

7. ## Re: ABS and LARGE Problem

This is just perfect! Thanks again both!

8. ## Re: ABS and LARGE Problem

Hello.

Amazing formula!

How do I do this on a non contiguous range?

Thanks in advance.

Filipe Caetano

9. ## Re: ABS and LARGE Problem

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.

ABCDE
11-61-6
2255
3-3-42-4
4-1-3-3
52-2-3-2
6522
7-62-12
80-1-1
9-4121
10-200
115
12
13-6
14
150
16
17-4
18
19-2

Sheet2

Worksheet Formulas
CellFormula
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.

10. ## Re: ABS and LARGE Problem

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

 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)}

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•