+ Reply to Thread
Results 1 to 9 of 9

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

  1. 09-06-2013, 06:07 PM #1
    amartino44
    amartino44 is offline
    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.
    Reply With QuoteRegister To Reply
  2. 09-06-2013, 06:12 PM #2
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,225

    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
    Reply With QuoteRegister To Reply
  3. 09-06-2013, 06:34 PM #3
    amartino44
    amartino44 is offline
    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?
    Reply With QuoteRegister To Reply
  4. 09-06-2013, 06:36 PM #4
    mikerickson
    mikerickson is offline
    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.
    Reply With QuoteRegister To Reply
  5. 09-06-2013, 06:40 PM #5
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    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"
    Reply With QuoteRegister To Reply
  6. 09-06-2013, 06:41 PM #6
    XOR LX
    XOR LX is offline
    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/
    Reply With QuoteRegister To Reply
  7. 09-06-2013, 06:54 PM #7
    amartino44
    amartino44 is offline
    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.
    Reply With QuoteRegister To Reply
  8. 09-06-2013, 07:43 PM #8
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,225

    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
    Reply With QuoteRegister To Reply
  9. 07-12-2017, 05:32 PM #9
    SantaBat
    SantaBat is offline
    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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. Seperating "colour" and "size" values into seperate columns from the "product name"
    By sy_lavery in forum Excel General
    Replies: 0
    Last Post: 01-09-2013, 06:52 PM
  3. Excel 2007 : Match Values and Return "Present" "Absent" "Per Diem" or remain blank
    By DanBraden in forum Excel General
    Replies: 3
    Last Post: 02-16-2011, 02:55 PM
  4. Formula returning result of "True" or "False" instead of "1" or "0"
    By AllenMead in forum Excel General
    Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  5. if statement see "Male" or "Female" returning "Y" otherwise leave blank
    By qwervbnmasdf in forum Excel General
    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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1