How to Use Excel's AGGREGATE Function to Refine Calculations

Quick Links

• The AGGREGATE Syntax

• Example 1: Using AGGREGATE to Ignore Errors

• Example 2: Using AGGREGATE to Ignore Hidden Rows (Reference)

• Example 3: Using AGGREGATE to Ignore Hidden Rows (Array)

• Things to Note When Using the AGGREGATE Function

Excel's AGGREGATE function lets you perform calculations whilst ignoring hidden rows, errors, or other functions that appear in the data. It's similar to the SUBTOTAL function but provides more calculation options and gives you more control over what you want to exclude from the calculation.

The AGGREGATE Syntax

Before we look at some examples of the AGGREGATE function in use, let's see how it works. The AGGREGATE function has two syntaxes—one for references and one for arrays—though you don't need to get yourself tied up in knots over which one you're using, as Excel selects the relevant one depending on the arguments you input. You can see both syntaxes in use when I show you some examples soon.

The Reference Form Syntax

The syntax for the reference form of the AGGREGATE function is:

=AGGREGATE(<em>a</em>,<em>b</em>,<em>c</em>,<em>d</em>)

where

• a (required) is a number that represents the function you want to use in the calculation,
• b (required) is a number that defines what you want the calculation to ignore,
• c (required) is the range of cells on which the function will be applied, and
• d (optional) is the first of up to 252 additional arguments that specify further ranges.

The Array Form Syntax

On the other hand, if you're working with arrays, the syntax is:

=AGGREGATE(<em>a</em>,<em>b</em>,<em>c</em>,<em>d</em>)

where

• a (required) is a number that represents the function you want to use in the calculation,
• b (required) is a number that defines what you want the calculation to ignore,
• c (required) is the array of values on which the function will be applied, and
• d is the second argument required by array functions like LARGE, SMALL, PERCENTILE.INC, and others.

Functions and Exclusions (Arguments a and b)

When entering arguments a and b in either syntax form above, you'll have various options to choose from.

The table below shows the different functions you can use in the AGGREGATE calculation (argument a). Even though you might be tempted to type the function name, remember that this argument must be a number that represents the function you want to use. Functions 1 to 13 are for use with the reference form syntax, and functions 14 to 19 are for use with the array form syntax.

Number	Function	What It Calculates
1	AVERAGE	The arithmetic mean
2	COUNT	The number of cells that contain numeric values
3	COUNTA	The number of cells that are not empty
4	MAX	The largest value
5	MIN	The smallest value
6	PRODUCT	A multiplication
7	STDEV.S	The simple standard deviation
8	STDEV.P	The population-based standard deviation
9	SUM	An addition
10	VAR.S	The simple variation
11	VAR.P	The population-based variance
12	MEDIAN	The middle value
13	MODE.SNGL	The most frequently occurring number
14	LARGE	The nth largest value
15	SMALL	The nth smallest value
16	PERCENTILE.INC	The nth percentile, with the first and last values included
17	QUARTILE.INC	The nth quartile, with the first and last values included
18	PERCENTILE.EXC	The nth percentile, with the first and last values excluded
19	QUARTILE.EXC	The nth quartile, with the first and last values excluded

This table lists the numbers you can input to exclude certain values when creating your AGGREGATE formula (argument b):

Number	What Is Ignored
0	Nested SUBTOTAL and AGGREGATE functions
1	Hidden rows, and nested SUBTOTAL and AGGREGATE functions
2	Errors, and nested SUBTOTAL and AGGREGATE functions
3	Hidden rows, error values, and nested SUBTOTAL and AGGREGATE functions
4	Nothing
5	Hidden rows only
6	Errors only
7	Hidden rows and errors

Now, let's look at some examples of how you can use the AGGREGATE function in real-world scenarios.

Example 1: Using AGGREGATE to Ignore Errors

This Excel spreadsheet contains a list of soccer players, the number of games they've played, the number of goals they've scored, and their game-per-goal ratios. Your aim is to work out the average game-per-goal ratio for all the players combined.

If you were to use the AVERAGE function alone by typing:

=AVERAGE(Player_Goals[Games per goal])

into cell C1, this would return an error, because the referenced range contains #DIV/0! errors.

Related

How to Fix Common Formula Errors in Microsoft Excel

Find out what that error means and how to fix it.

Posts

Instead, using the AGGREGATE function gives you the option to ignore these errors and return the average for the remaining data. To do this, in cell C2, you need to type:

=AGGREGATE(1,6,Player_Goals[Games per goal])

where

• 1 (argument a) represents the AVERAGE function,
• 6 (argument b) tells Excel to ignore errors, and
• Player_Goals[Games per goal] is the reference.

Example 2: Using AGGREGATE to Ignore Hidden Rows (Reference)

Using the same spreadsheet, your next target is to calculate the total number of goals the team has scored.

Example 3: Using AGGREGATE to Ignore Hidden Rows (Array)

Next, let's say you wanted to list the two highest goal tallies for players who have played 20 games or fewer.

You could apply the filter first and then generate your formula, but for the purposes of this demonstration, let's create the formula first.

In cell C1, type:

=AGGREGATE(14,5,Player_Goals[Goals scored],{1;2})

where

• 14 (argument a) represents the LARGE function,
• 5 (argument b) tells Excel to ignore hidden rows,
• Player_Goals[Goals scored] is the array of values, and
• {1;2} tells Excel that you want it to return the largest (1) and second-largest values (2) on separate rows (;).

When you press Enter, notice that the result is a spilled array covering cells C1 and C2 because you told Excel to return the top two values.

Related

Everything You Need to Know About Spill in Excel

It's not worth crying over spilled references.

Posts

Now, filter the Games Played column to include only those players who have played 20 games or fewer, and see that the result of the AGGREGATE formula you entered earlier changes to ignore the hidden rows.

Things to Note When Using the AGGREGATE Function

Before you go ahead and use the AGGREGATE function in your own Excel workbooks, take a moment to note the following pointers:

• Excel's AGGREGATE function works with vertical ranges only, not horizontal ranges. So, when you reference a horizontal range, AGGREGATE will not ignore rows in hidden columns.
• Argument c in the AGGREGATE formula cannot be the same cell or range of cells across multiple worksheets (also known as 3D references).
• Even though the AGGREGATE function is a great way to bypass errors in calculations, don't get into the habit of ignoring errors altogether. They're there for a reason and could help you troubleshoot issues with your data.
• The array form of the AGGREGATE function will not ignore hidden rows, nested subtotals, or nested aggregates if the array argument includes a calculation.

---

Another way to hide rows in Excel tables so that the AGGREGATE function only includes what's showing is by inserting slicers, interactive buttons that you can click to make filtering much more straightforward.

The above is the detailed content of How to Use Excel's AGGREGATE Function to Refine Calculations. For more information, please follow other related articles on the PHP Chinese website!