+ Reply to Thread
Results 1 to 7 of 7

Using Aggregate results in #NAME? error

  1. #1
    Forum Contributor
    Join Date
    02-15-2005
    Location
    Central UK
    MS-Off Ver
    Office 365
    Posts
    175

    Using Aggregate results in #NAME? error

    Hi All,

    I'm trying to produce a total of three non-adjacent cells, all cells are numbers, no negatives BUT for a linked graph to display I have to use NA() option.

    This creates an error if I try and total the three cells (=B10+B21+B32) so (after much web searching) I came across the AGGREGATE formula.

    The formula is (I thought) simply:
    =AGGREGATE( 9,6, B10,B21,B32)

    where 9 = SUM
    6 = Ignore all errors
    B10 = #N/A
    B21 = #N/A
    B32 = 1

    I expected the output to be 1 but instead I get #NAME?

    Can anyone help, please?

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Using Aggregate results in #NAME? error

    Do you have Excel 2010 or later? If not, AGGREGATE isn't available to you.
    Remember what the dormouse said
    Feed your head

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,081

    Re: Using Aggregate results in #NAME? error

    What version of Excel you got?
    Cheers
    Andy
    www.andypope.info

  4. #4
    Forum Contributor
    Join Date
    02-15-2005
    Location
    Central UK
    MS-Off Ver
    Office 365
    Posts
    175

    Re: Using Aggregate results in #NAME? error

    Arg! Excel 2007!

    Is there a workaround available?

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Using Aggregate results in #NAME? error

    You could use:
    =IFERROR(B10,0)+IFERROR(B21,0)+IFERROR(B32,0)

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Using Aggregate results in #NAME? error

    well normally you'd produce the na() because you don't want it to plot and use a helper range along the lines of
    =if(a1=0,na(),a1) why not sum the original range
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  7. #7
    Forum Contributor
    Join Date
    02-15-2005
    Location
    Central UK
    MS-Off Ver
    Office 365
    Posts
    175

    Re: Using Aggregate results in #NAME? error

    Thanks Guys - as brilliant as always! Appreciate all the comments, I used romperstomper's suggestion which does the job.

    Cheers

+ 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] Need to display ONLY filtered results by use of a Sum, Vlookup and aggregate?
    By shameus in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-27-2014, 04:02 PM
  2. [SOLVED] UDF .Volatile results to #Value! error
    By MONKgr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-20-2012, 11:02 AM
  3. Aggregate error
    By TomBP in forum Access Tables & Databases
    Replies: 3
    Last Post: 12-15-2010, 04:52 PM
  4. Best way to aggregate?
    By gnome_core in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-03-2009, 04:38 PM
  5. [SOLVED] 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