|
HTOTALS Function
Floating or fixed row totals: Totals each row of the Table_Range/Expression.
Floating produces a copy of the Table_Range/Expression with a vertical array of the totals of the rows appended at the right.
Fixed produces a fixed position vertical array of the totals of the rows without a copy of the table/range.
Dynamically adjusts to the number of rows and columns in Table_Range
HTOTALS is a multi-threaded, non-volatile array function.
HTOTALS Syntax
HTOTALS (Table_Range [, Exclude_Rows] [,TotalTypes] [,Fixed] )
Table_Range
Array, Table, Range or Spill reference whose columns are to be totalled.
Exclude_Rows (Optional. Default no rows will be excluded)
Optional. If omitted all rows will be totalled.
A list (array or range) identifying the rows 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:B2,A4) results in rows 1,2 and 4.
Alphabetic labels are used as lookup values in the first column of Table_range to find the rows to exclude. These labels can be supplied in an array or a range.
Relative row numbers are used directly.
These row numbers can be supplied in an array or range.
For example {2,4} excludes the second and fourth row of the Table_Range.
Negative numbers work from bottom to top so {-1, -2) excludes the last two rows of the Table_range.
Zero acts as an include operator.
{1,0,5} results in rows 1 through 5 inclusive
{1,5} results in row 1 and row 5 only
{5,0} results in row 5 and all rows after row 5
{0,5} results in the first 5 rows
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 rows.
HTOTALS and TOTALS can be nested: =HTOTALS(TOTALS(MyData)) gives a row of totals for myData at the bottom, a column of totals for MyData at the right, and a grand total in the bottom right corner.
Fixed (Optional. Default False)
Controls whether the Totals will be in a fixed but spilling across location starting from the cell containing the function, or will be floating as the last row of whatever size the dynamic array is.
|
