Spread.NET 17 Formula Reference
Formula Functions / Functions A to C / AGGREGATE
In This Topic
    AGGREGATE
    In This Topic

    This function calculates an aggregate value in a list or database.

    Syntax

    AGGREGATE(functionnum, options, reference1, reference2, …)

    or

    AGGREGATE(functionnum, options, array, k)

    Arguments

    This function has the following arguments:

    Argument Description
    functionnum Refers to the number code that specifies the function to use (see table below).
    options Refers to the numerical value that determines which values must be ignored in the evaluation range for the function (see table below)
    reference1 Refers to the argument for which you want the aggregate value.
    reference2 [Optional] Refers to the additional arguments for which you want the aggregate value.
    array Refers to an array, array formula, or a reference to a range of cells for which you want the aggregate value.
    k Refers to the additional arguments for which you want the aggregate value.

    The functionnum argument is the number that represents the built-in function to use, as given in this table.

    Built-In Function Function Code
    AVERAGE 1
    COUNT 2
    COUNTA 3
    MAX 4
    MIN 5
    PRODUCT 6
    STDEV.S 7
    STDEV.P 8
    SUM 9
    VAR.S 10
    VAR.P 11
    MEDIAN 12
    MODE.SNGL 13
    LARGE 14
    SMALL 15
    PERCENTILE.INC 16
    QUARTILE.INC 17
    PERCENTILE.EXC 18
    QUARTILE.EXC 19

    The options argument has the following options.

    Option Description
    0 or omitted Ignore nested SUBTOTAL and AGGREGATE functions
    1 Ignore hidden rows and nested SUBTOTAL and AGGREGATE functions
    2 Ignore error values and nested SUBTOTAL and AGGREGATE functions
    3 Ignore hidden rows, error values, and nested SUBTOTAL and AGGREGATE functions
    4 Ignore nothing
    5 Ignore hidden rows
    6 Ignore error values
    7 Ignore hidden rows and error values

    Remarks

    This function is designed for columns of data, or vertical cell range. This function has an option to ignore hidden rows and error values.

    If a second reference argument is required but it is not provided by the user, a #VALUE! error is returned. Also, if one or more of the references are 3-D references, a #VALUE! error value is returned.

    The reference2 argument is optional; however, the following functions are required in this argument.

    Data Types

    Accepts numeric values. Returns numeric data.

    Examples

    AGGREGATE(14,6,B1:B7,3) gives the result 5, where B1:B7 is the cell range containing a numeric list.

    Version Available

    This function is available in Spread for Windows Forms 11.0 or later.