+ Reply to Thread
Results 1 to 9 of 9

Finding the largest absolute values but returning the "+" "-" sign

  1. #1
    Forum Contributor
    Join Date
    12-12-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    448

    Finding the largest absolute values but returning the "+" "-" sign

    Hallo. I have found this wonderful formula. =LARGE(ABS(IF(ISNUMBER($F$29:$F$340),$F$29:$F$340)),ROWS($1:1)) It find the largest absolute values in a column. HOWEVER, I keep running into problems. The current one is that I want the values to be in order of absolute value but I would still like to see if the original value was positive or negative. The formula just shows the value as absolutes...no negative or positive signs Helpie.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,243

    Re: Finding the largest absolute values but returning the "+" "-" sign

    If you gave some feedback to folks who try to help you, like in this post:

    http://www.excelforum.com/excel-gene...01#post3396701

    then they might be willing to help you further.

    Pete

  3. #3
    Forum Contributor
    Join Date
    12-12-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    448

    Re: Finding the largest absolute values but returning the "+" "-" sign

    I do give feedback. I gave you reputation It worked. Do you know the answer to this one?

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Finding the largest absolute values but returning the "+" "-" sign

    The easiest would be to create a helper column, sort on that, but take the values from the original column.

    What do you do with the OP array formula, put it in cells or use it for further computation? (i.e. how ugly a work-around would be acceptable)
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  5. #5
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    Re: Finding the largest absolute values but returning the "+" "-" sign

    Try this Array Formula;

    =INDEX(A:A,LOOKUP(9.9E+300,MATCH(LARGE(IFERROR(ABS(A$2:A$100),0),1)*{1,-1},A:A,0)))

    Then use Custom cell format as; +#,##0.00;-#,##0.00

    Adjust the range.
    Last edited by Haseeb Avarakkan; 09-06-2013 at 06:42 PM.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Finding the largest absolute values but returning the "+" "-" sign

    These all appear to be related to the same formula if I'm not mistaken:

    Max Absolute Array change to 1st, 2nd, largest etc 09:33 PM

    Copying down a formula 10:01 PM

    Problem with =LARGE(ABS($F$29:$F$340),1) for blank rows 10:11 PM

    Finding the largest absolute values but returning the "+" "-" sign 11:07 PM

    Perhaps it might be an idea to either stay within one thread or to have a think about what it is you really want to achieve before making so many related posts.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  7. #7
    Forum Contributor
    Join Date
    12-12-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    448

    Re: Finding the largest absolute values but returning the "+" "-" sign

    @mikerickson. What would a helper column involve? I put it in cells, so I can see what the large items are. No further computation.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,243

    Re: Finding the largest absolute values but returning the "+" "-" sign

    Quote Originally Posted by amartino44 View Post
    I do give feedback. I gave you reputation It worked.
    There was no comment back from you in that thread, although it is clear from your first post in this thread that you have taken on board my suggestion. Also, I have not received rep from you - perhaps you gave it to Debraj.

    Pete

  9. #9
    Registered User
    Join Date
    10-08-2016
    Location
    Barbados
    MS-Off Ver
    http://www.kniga-onlain.ru/
    Posts
    1

    Re: Finding the largest absolute values but returning the "+" "-" sign

    Нашла интересные сайты.
    Спешу поделиться с вами.Сама также как вы искала подобные сайты.
    Где взять кредит,где получить кредит,взять кредит онлайн,что такое биткоин,где купить биткоин,как купить биткоин,где обменять биткоин ? ответы тут: http://agent-banka.ru/
    читать книги онлайн бесплатно и без регистрации http://kniga-onlain.ru/
    рецепты простых и вкусных блюд http://country-food.ru/
    уроки фотошоп онлайн для начинающих бесплатно http://photoshop-gid.ru/
    как сделать и заработать на своём сайте продавая рекламу http://global-control.ru/
    Читать книги онлайн бесплатно и без регистрации http://www.best-businessman.ru
    Russian literature is free. online Library http://www.best-businessman.ru
    домашние рецепты на скору руку http://life-moscow.com/
    библиотека онлайн где можно читать книги бесплатно и без регистрации http://onlain-kniga.ru/
    список бирж ссылок для заработка на сайте http://global-control.ru/
    что такое биткоин http://partner-banka.ru/
    как купить биткоин http://partner-banka.ru/
    где заработать биткоин http://partner-banka.ru/
    как заработать биткоин http://partner-banka.ru/
    http://www.best-businessman.ru/

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Expanded and collapsed the rows by toggle "+" or "-" sign on left of the text
    By Rocky2013 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-24-2013, 02:49 PM
  2. Replies: 0
    Last Post: 01-09-2013, 06:52 PM
  3. Replies: 3
    Last Post: 02-16-2011, 02:55 PM
  4. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  5. Replies: 3
    Last Post: 12-14-2006, 01:36 PM

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