Excel AGGREGATE Function allows you to perform various calculations on a range of data, providing flexibility and control over the operations you want to perform. It can handle functions like SUM, AVERAGE, MAX, MIN, and more, and even ignore errors or hidden rows, making it a versatile tool for data analysis.
Syntax
AGGREGATE(function_num, options, ref1, [ref2], [k])
Arguments
| function_num | The number that represents the function to use for the calculation. |
| options | The option specifying how to handle hidden rows, errors, or other specific behavior. |
| ref1 | The first reference or range for the calculation. |
| [ref2] | (Optional) A second reference or range for functions that require two ranges. |
| [k] | (Optional) A parameter that adjusts the behavior of the function, used for functions where it is relevant. |
How to use
1. Start with the function_num, which is a number representing the function you want to apply. This can be a number from 1 to 19, each corresponding to a different Excel function, such as 1 for AVERAGE, 2 for COUNT, 3 for MAX, and so on.
2. Choose the options argument. This number defines the behavior of the AGGREGATE function. It can be any number from 0 to 7 or 9 to 13, depending on what you want to do. For example, using 0 will ignore hidden rows and errors, while using 6 will ignore only hidden rows.
3. Specify the first reference (ref1), which is the range of data you want to apply the chosen function to. This is the primary data source for the calculation.
4. Optionally, provide a second reference (ref2) when the selected function requires two ranges for calculation. Not all functions require this, so it’s often left empty.
5. If the function you’ve chosen involves a “k” parameter, add it as the last argument. This parameter is optional for many functions and can be used to fine-tune the behavior of the function, depending on the selected function_num.
Here’s an example of how to use the AGGREGATE function:
|
1 |
=AGGREGATE(1, 6, A1:A10) |
In this example, we’re calculating the AVERAGE of values in the range A1:A10, while ignoring hidden rows but considering errors. The function_num 1 corresponds to AVERAGE, and option 6 tells it to ignore hidden rows.
Additional information
If you’re looking for more details on the available function numbers, options, or need specific information on a particular function, you can refer to the official Excel documentation.