+ Reply to Thread
Results 1 to 3 of 3

Min/Max of absolute value, show negative if negative

  1. #1
    Forum Contributor
    Join Date
    03-04-2013
    Location
    Europe
    MS-Off Ver
    Office 365 ProPlus
    Posts
    147

    Min/Max of absolute value, show negative if negative

    I'm trying to find the largest and smallest absolute value of a range. This can be done with

    Please Login or Register  to view this content.
    and then pressing ctrl+shift+enter. However, that will give me a positive number even if the smallest/largest absolute value was negative. I would like the number to be negative if it originally was negative.

    I was thinking of doing something like

    Please Login or Register  to view this content.
    but i don't quite remember the MATCH function.. There might be an easier way though?

    I've attached an example workbook. I would like to find the largest and smallest absolute value of the range, and if negative, the number should show as negative.

    Thankful for any help!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    03-04-2013
    Location
    Europe
    MS-Off Ver
    Office 365 ProPlus
    Posts
    147

    Re: Min/Max of absolute value, show negative if negative

    Solved it using

    Please Login or Register  to view this content.
    calculated as an array (ctrl+shift+enter).

    Someone might have a more elegant solution though?

  3. #3
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Min/Max of absolute value, show negative if negative

    =IFERROR(VLOOKUP(MIN((ABS(A4:A278)));A4:A278;1;0);MIN((ABS(A4:A278)))*-1)
    Try this array formula

    It's almost the same
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

+ 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