Let’s consider each of the arguments in the function. Excel will adopt the necessary form based on the function_num selected. You do not need to worry about which form you are using. Reference form: =AGGREGATE(function_num,options,ref1.)Īrray form: =AGGREGATE(function_num,options,array,) The AGGREGATE function can have two syntax forms: But if you do, you’ll know this is a big deal. If you don’t know what that means, don’t worry. The screenshot below, in which row 5 is hidden, shows the average of the visible cells should be 38,750 (as calculated by AGGREGATE), not 71,000 (as calculated by AVERAGE).Īs a bonus, AGGREGATE can also process arrays for specific functions without the need for Ctrl + Shift + Enter. We can tell the AGGREGATE function to ignore hidden cells, enabling it to calculate the correct result. The AVERAGE function will calculate using all the cells in the range it does not care whether those cells are visible or not. The screenshot below shows the result of both the LARGE and AGGREGATE functions.Īs a second example, what if you want to find the average of the visible cells. We can tell the AGGREGATE function to ignore the error, enabling it to calculate the correct result. We would typically turn to the LARGE function for this type of calculation, but in this circumstance, LARGE will return an error. Let’s say you want to find the 2nd largest result over a range of cells where those cells contain an #N/A error. To help you see the power, let’s consider some scenarios. But it’s better than those functions because it performs all of those whilst ignoring errors, hidden cells and other cells containing AGGREGATE and SUBTOTAL functions. That sounds the same as SUM doesn’t it? Hence the problem with its name, the AGGREGATE function does so much more than SUM.ĪGGREGATE can COUNT, AVERAGE, MAX, SMALL and SUM, to name just a few. What does the word aggregate mean? To form a whole from separate parts. My aim with this post is to show you how good AGGREGATE is, so that you can make use of its power. It is one of the most powerful functions in Excel, yet I’ve rarely seen it used in a real-life scenario. In Excel 2010, Microsoft introduced a new function called AGGREGATE.
0 kommentar(er)
