+ Reply to Thread
Results 1 to 13 of 13

How do I locate the largest integer in an array?

  1. #1
    Registered User
    Join Date
    11-13-2008
    Location
    Colorado
    Posts
    89

    How do I locate the largest integer in an array?

    Sorry if this has been asked before and it seems simple.

    I have an array of data with both positive and negative numbers. I want to locate the largest number (farthest from zero) regardless of sign. But I will need to retain the sign because I will be dividing every number in the array by that number to normalize the array to values between 1 and -1?

    Thanks in advance!

  2. #2
    Registered User
    Join Date
    05-02-2004
    MS-Off Ver
    2007
    Posts
    43
    Is this VBA or a formula?
    TJ

  3. #3
    Forum Contributor mewingkitty's Avatar
    Join Date
    09-29-2008
    Location
    Fort McMurray, Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    949

    Formula

    as a formula
    Please Login or Register  to view this content.
    confirmed with CTRL + SHIFT + ENTER
    to enter it as an array.

    EDIT
    Sry,
    to retain the + or -

    Please Login or Register  to view this content.
    again entered with CTRL + SHIFT + ENTER
    Last edited by mewingkitty; 01-15-2009 at 05:08 PM.
    =IF(AND(OR(BLONDE,BRUNETTE,REDHEAD),OR(MY PLACE,HER PLACE),ME),BOW-CHICKA-BOW-WOW,ANOTHER NIGHT ON THE INTERNET)

  4. #4
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    You can loop through the array to determine the largest absolute value, then assign the array location to a variable to determine the actual value. For example:

    Please Login or Register  to view this content.
    HTH

    Jason

  5. #5
    Registered User
    Join Date
    11-13-2008
    Location
    Colorado
    Posts
    89
    Formula

    But using the Max(Abs(.... returns the positive value of the largest integer? Actually it returned the wrong integer in my sheet.

  6. #6
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Whoops. Ignore my previous post. This was posted in the incorrect forum, so I thought it was a VBA array question.

  7. #7
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    mewingkitty's formula above was very close, but I just changed one number:

    =INDEX(A1:A4,MATCH(MAX(ABS(A1:A4)),ABS(A1:A4),0))

    HTH

    Jason

  8. #8
    Forum Contributor mewingkitty's Avatar
    Join Date
    09-29-2008
    Location
    Fort McMurray, Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    949

    hm

    That's odd, gimme a min.

    Was trying to cut out a step, and didn't double-check the effects.

    See attached.

    EDIT
    Ah, thanks Jason, I didn't realize I entered "1" there.
    Attached Files Attached Files
    Last edited by mewingkitty; 01-15-2009 at 05:21 PM.

  9. #9
    Registered User
    Join Date
    11-13-2008
    Location
    Colorado
    Posts
    89
    Mewingkitty & Jason

    Attached is the file...

    The column is AI2:102

    The largest integer is -600 in AI102

    I put your formula in cell AP3 but I got an error.
    Attached Files Attached Files

  10. #10
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Largest number in an array...ignoring sign

    Possible non-array solution:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  11. #11
    Forum Contributor mewingkitty's Avatar
    Join Date
    09-29-2008
    Location
    Fort McMurray, Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    949
    Confirming with CTRL + SHIFT + ENTER,
    means holding the control and shift keys while hitting enter,
    instead of just enter.
    This creates an array.

    Check out Ron Coderre's approach for a non-array solution.

  12. #12
    Registered User
    Join Date
    11-13-2008
    Location
    Colorado
    Posts
    89
    I solved it with an IF and MIN and Max statements...But this will mean I will eventually have around 50 thousand If statements added into a file that already takes 20 seconds to open. So is there a better way?
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    Both formulas presented above will work.

    Ron's:
    =LOOKUP(10^99,1/(ABS(AI2:AI102)=MAX(INDEX(ABS(AI2:AI102),0))),AI2:AI102)

    mewingkitty's:
    =INDEX(AI2:AI102,MATCH(MAX(ABS(AI2:AI102)),ABS(AI2:AI102),0))

    Your file posted above is correct, just select the cell, go into edit mode (hit F2), then hold down CTRL and SHIFT while hitting ENTER.

    Jason

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1