+ Reply to Thread
Results 1 to 2 of 2

Learn to use the Aggregate Function

  1. #1
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,720

    Learn to use the Aggregate Function

    Here is a great link to learn about this powerful excel function

    AGGREGATE

    https://exceloffthegrid.com/aggregat...ure-not-using/

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Learn to use the Aggregate Function

    FWIW, I find AGGREGATE's ability to skip error values as problematic as IFERROR. There can be good reasons to skip all #N/A and #VALUE! errors, also many but not all #DIV/0! and #NUM! errors, but there's almost never a good reason NOT to propagate #REF!, #NULL! and #NAME? errors, and in the latest Excel also #CALC! and #SPILL!. If only it were easier to specify which error values to skip and which to propagate than using (latest Excel) constructs like

    LET(x,some_arcane_expression,IFERROR(IF(AND(ERROR.TYPE(x)<>{3,7}),x),x))

    which returns nonerrors, FALSE for #N/A and #VALUE!, and all other error values as-is.

    Why is this distinction needed? Because #N/A is a proxy for missing values. Excel needs a missing data pseudovalue. Also, it's an expected return value from unsuccessful lookups and MATCH calls. Then there's #VALUE! which can come from trying to use nonnumeric text in arithmetic operations or as an expected return value from unsuccessful FIND and SEARCH. To repeat, Excel REALLY needs a #MISSING! pseudovalue. #DIV/0! is an expected return value from trying to average ranges or arrays with no numeric values, and #NUM! is an expected return value for a few arithmetic operations involving negative numbers. Unfortunately, real #DIV/0! errors from explicitly dividing anything by 0 should propagate, and #NUM! errors due to actual underflow/overflow (and Excel's continuing inability to use hardware floating point for MOD) should do so also. All other errors SHOULD BE unexpected, so should never be trapped.
    Last edited by hrlngrv; 10-28-2020 at 03:40 PM.

+ 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. IF and aggregate function
    By lacombeg in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-28-2020, 12:59 PM
  2. Aggregate function sum
    By barrenaj in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-16-2019, 12:27 PM
  3. [SOLVED] VBA Aggregate Function
    By Gregor y in forum Access Programming / VBA / Macros
    Replies: 9
    Last Post: 02-27-2017, 03:17 AM
  4. [SOLVED] AGGREGATE Function
    By chief_abound in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-03-2015, 12:48 AM
  5. Aggregate Function Using Sum
    By gtbaseball7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-01-2015, 02:19 PM
  6. Aggregate Function
    By nav505 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-04-2013, 03:20 AM
  7. Aggregate function
    By stefantem in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-19-2006, 04:47 AM

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