The values of rows hidden by a filter are always excluded irrespective of the “function_num” argument. When the value of “function_num” is between 101-111, the SUBTOTAL excludes the manually hidden rows from the calculations.When the value of “function_num” is between 1-11, the SUBTOTAL includes the manually hidden rows in the calculations.The values of the “function_num” argument have the following implications: The “function_num” argument can take either of these values depending on whether the hidden rows are included or excluded from the subtotals. One must observe that for every operation (function), there are two values (like 1 and 101 for AVERAGE) of the “function_num” argument. The operations (functions) performed by the SUBTOTAL function and the corresponding function numbers are listed as follows: Function The SUBTOTAL function performs an arithmetic operation depending on the value of the “function_num” argument. Operations Performed by the SUBTOTAL Function
Note: The “function_num” argument is always entered as a numeric value. The SUBTOTAL function returns a numeric output. The arguments “function_num” and “ref1” are mandatory, while “ref2” is optional. This also can be entered as a reference or as a named range. Ref2: This is the second range of cells on which the arithmetic operation is to be performed.It can be supplied as a reference or as a named range. Ref1: This is the range of cells on which the arithmetic operation is to be performed.The “function_num” argument can take any value from 1 to 11 or 101 to 111. Function_num: This is the number that determines which arithmetic operation (function) will be performed by the SUBTOTAL function.The SUBTOTAL function accepts the following arguments: The syntax of the SUBTOTAL function of Excel is shown in the following image: This can perform more operations compared to the SUBTOTAL excel function. In the modern versions of Excel, the AGGREGATE function AGGREGATE Function AGGREGATE Function in excel returns the aggregate of a given data table or data lists. The SUBTOTAL is a versatile function which is available in all versions of Excel. The SUBTOTAL function is categorized under the Math and Trigonometry functions of Excel. It can perform 11 different arithmetic operations on a dataset. The SUBTOTAL function works with vertical ranges (columns) of a dataset. However, the filtered-out values (the values hidden by a filter) are excluded, by default. With the SUBTOTAL function, one can either include or exclude the values of the hidden rows. The number 1 in the formula tells Excel that the average of the listed numbers is to be calculated. This output is the average of the given range (A1:A4). The formula “=SUBTOTAL(1,A1:A4)” returns 29. This function number is supplied as an argument to the SUBTOTAL function.įor example, a worksheet consists of the following values in column A: Each operation has a unique function number assigned to it. The SUBTOTAL excel function performs different arithmetic operations like average, product, sum, standard deviation, variance etc., on a defined range.
0 kommentar(er)
