﻿ AGGREGATE | Spread 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, reference1reference2, …)

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.

• LARGE
• SMALL
• PERCENTILE.INC
• QUARTILE.INC
• PERCENTILE.EXC
• QUARTILE.EXC

#### 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.