+ Reply to Thread
Results 1 to 5 of 5

AGGREGATE function, If 0 show no value

  1. #1
    Registered User
    Join Date
    04-02-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    97

    AGGREGATE function, If 0 show no value

    Looking for a minor enhancement to my sheet.

    I have a list of descriptive text in Column A, column D contains values for that text.

    In column G I have an aggregate function ranking TOP 10 values (Column D).

    EG: =AGGREGATE(14,6,$D$4:$D$200,ROWS($1:1)) to =AGGREGATE(14,6,$D$4:$D$200,ROWS($1:10)) for the top 10.

    Since these values are formatted to NUMBER with 2 decimal (0.65) to custom format "General;General;;@" doesn't work in this case.

    Problem is, sometimes D4:D200 only has 4 values therefore the remainder of the Top 10 has 0.00 as the result but would like to show NULL, nothing etc.

    Is there a way to do that?

    Thank you so much

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: AGGREGATE function, If 0 show no value

    I get a #NUM error. So...

    =IFERROR(AGGREGATE(14,6,$D$4:$D$200,ROWS($1:1)),"")
    and drag down.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    04-02-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    97

    Re: AGGREGATE function, If 0 show no value

    Thanks so much!, Rep added!

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: AGGREGATE function, If 0 show no value

    You're welcome..... and thanks. i really wasn't sure about this - when you said "sometimes D4:D200 only has 4 values therefore the remainder of the Top 10 has 0.00 as the result". Never mind, though - you're sorted.

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: AGGREGATE function, If 0 show no value

    be aware that :
    IFERROR hides ALL errors ( which may not be what you want)
    using the null text string in case of an error can lead to other errors if you need the outcome for further calculations ( you could keep the 0 and uncheck " show zero values " in XL options)

+ 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. Aggregate Function Using Sum
    By gtbaseball7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-01-2015, 02:19 PM
  2. Aggregate function to number rows
    By cdumper in forum Excel General
    Replies: 9
    Last Post: 09-27-2014, 07:22 PM
  3. Aggregate Function
    By nav505 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-04-2013, 03:20 AM
  4. Problem with Aggregate Function ADO SQL
    By Kyle123 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-10-2010, 07:44 AM
  5. Aggregate function
    By stefantem in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-19-2006, 04:47 AM
  6. [SOLVED] show aggregate total in pie chart
    By ecav in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-28-2005, 03:05 AM
  7. SQL - As part of an aggregate function ERROR
    By dave k in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-07-2005, 12:06 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