The reference 1 – is the mandatory argument indicating the named range for finding the subtotals.
STDEVP (standard deviation of the population). STDEV (standard deviation from the sample). COUNTAIN (the number of non-empty cells). The number of function - is a number from 1 to 11, which shows the statistical function for calculating subtotals: Syntax: the number of function, the reference 1 the reference 2. The «INTERMEDIATE» function returns the subtotal in the list or in the database. We remove the tick «Replace current subtotals», and in the «Use function:» field, select «Average».įormula «Subtotals» in Excel: the examples Add the average sales for each group of products. We have already designated the operation «Sum». The team subtotals allows you to use several statistical functions simultaneously. In order for intermediate data to be displayed OVER the group, remove the «Summary below data» condition. In order for each intermediate result to be followed by a page break, in the dialog box you need to tick the «Page break between groups». If you collapse the rows in the subgroups (click on the «minus» to the left of the line numbers), then we get the table only from the subtotals:Įach time the column «Set of furniture» is changed, the subtotal is recalculated in the «Sales» column. The initial table acquires the following form: In the «Add subtotal to» field, you should mark the columns to which the function will apply (Sales). In the field «Use function:» we assign the function «Sum». In the field «At each change in» we select the condition for data selection (in this example, «Value»). We fill the dialog box in the «Subtotal». The group «Outline» – is the team «Subtotal». We sort the range by the value of the first column - the same type of data should be near. There are no empty rows or columns in the table. The first line - shows the column names. The table is organized in the form of the simple list or database. In order for the function to produce the correct result, check the range for the following conditions: Therefore, we will use another Microsoft Excel tool – the «Interim Results» command. But the values will have to be calculated manually. If you use a filter, you can get the same type of records according to the specified selection criteria. It is necessary to calculate the revenue from the sale of certain groups of goods. We suppose that a user has a list with the sales of certain goods: To demonstrate the calculation of subtotals in Excel, let's take the small example.
0 kommentar(er)
