Page 1 of 2 12 LastLast
Results 1 to 10 of 14

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

  1. #1
    Board Regular tljenkin's Avatar
    Join Date
    Jun 2007
    Location
    London
    Posts
    146
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    Board Regular tljenkin's Avatar
    Join Date
    Jun 2007
    Location
    London
    Posts
    146
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ABS and LARGE Problem

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

  4. #4
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ABS and LARGE Problem

    You're welcome!

  5. #5
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,688
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default 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?
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  6. #6
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ABS and LARGE Problem

    Quote Originally Posted by pgc01 View Post
    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. #7
    Board Regular tljenkin's Avatar
    Join Date
    Jun 2007
    Location
    London
    Posts
    146
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ABS and LARGE Problem

    This is just perfect! Thanks again both!

  8. #8
    New Member
    Join Date
    Nov 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ABS and LARGE Problem

    Hello.

    Amazing formula!

    How do I do this on a non contiguous range?

    Thanks in advance.

    Filipe Caetano

  9. #9
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,639
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default 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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  10. #10
    New Member
    Join Date
    Nov 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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