Because that column contains #N/A error & hidden row or blank value. It returns an #N/A error value due to an error in that range. If you run the =SUM (D8 :D16) function directly in cell D17, In the AGGREGATE formula, when an appropriate option is used, the AGGREGATE in Excel gives the correct SUM value, neglecting the hidden row & error value. In the range D8:D16, cell D9 is a blank cell or hidden row & D12 contains an error value ( #N/A). In column D, for the 2017 yearly sales data. The following table contains yearly sales data (2017) When an appropriate option is used in the AGGREGATE function, the AGGREGATE in Excel returns or gives the SUM of the remaining values neglecting the error value in cells C11 & C12.
We are calculating the SUM here to omit the value of k.
‘k’ is an optional argument used only for functions like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC. The system has chosen it as an array of numeric values. Here reference range of values is C8:C16. Array: It is a range for which you want to calculate aggregate functions. To ignore these errors, we will select Option 6 (Ignore error values) For 2016-year sales data, In the range C8:C16, a cell C11 & C12 contains an error value (#DIV/0! & #N/A). To ignore the error values, we have to use option 6 in an AGGREGATE function Because that column contains #DIV/0! & #N/A errors. It returns an error value due to an error in that range. If you run the =SUM (C8:C16) function directly in cell C17, In the range C8:C16, a cell C11 & C12 contains an error value (#DIV/0! & #N/A) in the AGGREGATE formula, when an appropriate option is used, the AGGREGATE in Excel gives the correct SUM value, neglecting the error value. In column C, for the 2016 yearly sales data. The following table contains yearly sales data (2016) ‘k’ is an optional argument, and is used only for a function like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC. It is selected as an array of numeric values Here reference range of values is B8:B16. Array: is a range for which you want to calculate aggregate functions. so we will select Option 4 (ignore nothing) For 2015-year sales data, all values are given, and we won’t have to ignore any values it does not contain hidden rows, errors & nested subtotals. Function_ num: For the SUM function, the function_ num is 9. Here will calculate the SUM using an AGGREGATE function in cell B19. It gives the correct value because that column does not contain hidden rows, errors & nested subtotals 
If you run the =SUM (B8:B16) function directly in cell B17, The following table contains yearly sales data (2015)
CREATE AVERAGE OF PERCENTAGES IN EXCEL DOWNLOAD
You can download this AGGREGATE Function Excel Template here – AGGREGATE Function Excel Template Example #1
0 kommentar(er)
