|
TOTALS & AGGREGATES Functions
Floating or fixed column totals: Totals each column of the Table_Range/Expression.
Floating produces a copy of the Table_Range/Expression with a horizontal array of the totals of the columns appended at the bottom.
Fixed produces a fixed position horizontal array of the totals of the columns without a copy of the table/range.
Dynamically adjusts to the number of rows and columns in Table_Range
TOTALS and AGGREGATES are multi-threaded, non-volatile array functions.
AGGREGATES is an alias of TOTALS
TOTALS/AGGREGATES Syntax
TOTALS (Table_Range [, Exclude_Cols] [,TotalTypes], [Fixed] )
AGGREGATES (Table_Range [, Exclude_Cols] [,TotalTypes], [Fixed] )
Table_Range
Array, Table, Range or Spill reference whose columns are to be totalled.
Exclude_Cols (Optional. Default no columns will be excluded)
Optional. If omitted all columns will be totalled.
A list (array or range) identifying the columns that are not to be totalled, given as one of:
If Table_Range is not an array: One or more ranges within Table_Range. Multiple disjoint ranges must be enclosed in ( )
– for example (A1:B1,D1) results in columns 1,2 and 4.
Alphabetic labels are used as lookup values in the first row of Table_Range to find the excluded columns. These labels can be supplied in an array or a range.
•Relative column numbers are used directly.
These column numbers can be supplied in an array or range.
For example {2,4} excludes the second and fourth column of the Table_Range.
Negative numbers work from right to left so {-1, -2) excludes the last two columns of the Table_Range.
Zero acts as an include operator.
{1,0,5} results in Columns 1 through 5 inclusive
{2,0,-2} results in the second column and all columns up and including the second-to-last column
{1,5} results in Column 1 and Column 5 only
{5,0} results in Column 5 and all Columns after Column 5
{0,5} results in the first 5 Columns
TotalTypes (Optional, Default 9 =SUM)
An array/range of types to control the type of totalling to be done. Allows for different total types for different columns.
1 = Average
2 = Count
3 = CountA
4 = Max
5 = Min
6 = Product
7 = Stdev
8 = stdevp
9 = Sum
10 = Var
11 = Varp
12 = Median
13 = Mode.Sngl
Empty, Zero, Missing or non-numeric values within the array/range will be treated as 9=SUM.
If only a single TotalType is given it will be used for all columns.
Fixed (Optional. Default False)
If TRUE the Totals will be in a fixed but spilling across location starting from the cell containing the function, if FALSE the Totals will be floating as the last row of whatever size the dynamic array is.
TOTALS Example:
=TOTALS(SORT(FILTER(Sales,Sales[Region]=M6),6,-1),9,{1,2,3,5})
The TOTALS function is wrapped around a dynamic array expression, so that as the number of rows spilled by the dynamic array expression changes the totals are always the last row.
The TotalType is 9 which means SUM.
Columns 1,2,3 and 5 are excluded from the totals.
.png)
Accessing the floating Totals from a formula.
You can use the SLICES function to access the floating Totals Row. If the dynamic array formula is in K9 then
=SLICES(K9#,-1) gives the complete total row (-1 means the first row at the bottom),
and =SLICES(K9#,-1,-1) gives 1560.02, the last column in the last row.
To calculate Yield = Total Revenue/Total Units you could use the formula
= SLICES(K9#,-1,-1)/SLICES(K9#,-1,-3)
Adding a Total heading to the Totals row
You can use the SPLICECELLS function to replace cells in a dynamic array.
So to add the word “Totals” into the floating totals row wrap the TOTALS formula in
SPLICECELLS(total formula,-1,1,”Totals”)
The -1 specifies the last row of the dynamic array (which is the totals row) and the 1 specifies the first column.
.png)
|
