Difference between revisions of "Manuals/calci/SUBTOTAL"
Jump to navigation
Jump to search
(Created page with "<div id="16SpaceContent" align="left"><div class="ZEditBox" align="justify"> Syntax </div></div> ---- <div id="4SpaceContent" align="left"><div class="ZEditBox" align=...") |
|||
| Line 1: | Line 1: | ||
| − | + | =SUBTOTAL(SomeType, SomeArray)= | |
| − | + | where, | |
| + | *<math>SomeType</math> specifies which function type to use within a list, | ||
| + | *<math>SomeArray</math> is set of values or range of cells containing values. | ||
| − | + | SUBTOTAL() calculates a subtotal in a list or database. | |
| − | |||
| − | |||
| − | + | == Description == | |
| − | + | SUBTOTAL(SomeType, SomeArray) | |
| − | |||
| − | |||
| − | + | *SUBTOTAL() function is designed for columns of data or vertical range of cells. | |
| + | *Argument <math>SomeType</math> specifies which function to use in calculating subtotal from the list of functions. <math>SomeType</math> can be any number between 1 to 11 (that includes hidden values) or 101 to 111 (that ignores hidden values). | ||
| + | *Below table describes the Function Type and the number associated with it. | ||
| + | {| class="wikitable" | ||
| + | |- | ||
| + | ! Function Type !! Function Number(includes hidden values) !! Function Number(ignores hidden values) | ||
| + | |- | ||
| + | | AVERAGE || 1 || 101 | ||
| + | |- | ||
| + | | COUNT || 2 || 102 | ||
| + | |- | ||
| + | | COUNTA || 3 || 103 | ||
| + | |- | ||
| + | | MAX || 4 || 104 | ||
| + | |- | ||
| + | | MIN || 5 || 105 | ||
| + | |- | ||
| + | | PRODUCT || 6 || 106 | ||
| + | |- | ||
| + | | STDEV || 7 || 107 | ||
| + | |- | ||
| + | | STDEVP || 8 || 108 | ||
| + | |- | ||
| + | | SUM || 9 || 109 | ||
| + | |- | ||
| + | | VAR || 10 || 110 | ||
| + | |- | ||
| + | | VARP || 11 || 111 | ||
| + | |} | ||
| − | < | + | *<math>SomeArray</math> is an array of values or reference to cells containing values, for which the subtotal is to be calculated. |
| − | + | *Text values or empty cells are ignored. | |
| − | + | *For invalid argument values, Calci displays an error message. | |
| − | |||
| − | |||
| − | + | == Examples == | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
<div id="6SpaceContent" class="zcontent" align="left"> | <div id="6SpaceContent" class="zcontent" align="left"> | ||
{| id="TABLE3" class="SpreadSheet blue" | {| id="TABLE3" class="SpreadSheet blue" | ||
|- class="even" | |- class="even" | ||
| − | + | | class=" " | 40 | |
| − | + | | class=" " | | |
| − | + | ||
| − | | class=" " | | ||
| − | | class=" " | | ||
| − | |||
|- class="odd" | |- class="odd" | ||
| − | + | | class="sshl_f" | 50 | |
| − | |||
| − | |||
| − | | class="sshl_f" | | ||
| class="sshl_f" | | | class="sshl_f" | | ||
| + | |||
|- class="even" | |- class="even" | ||
| − | + | | class="sshl_f" | 25.5 | |
| − | |||
| − | | class="sshl_f | ||
| − | |||
| − | |||
| class="sshl_f" | | | class="sshl_f" | | ||
| + | |||
|- class="odd" | |- class="odd" | ||
| − | + | | class="sshl_f" | 80 | |
| − | |||
| − | |||
| − | | class="sshl_f" | | ||
| class="sshl_f" | | | class="sshl_f" | | ||
| + | |||
|- class="even" | |- class="even" | ||
| − | + | | class="sshl_f" | 15.5 | |
| − | |||
| − | |||
| − | | class="sshl_f" | | ||
| class="sshl_f" | | | class="sshl_f" | | ||
| + | |||
|- class="odd" | |- class="odd" | ||
| − | + | | class="sshl_f" | -2 | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | | class="sshl_f" | ||
| − | |- | ||
| − | |||
| − | |||
| − | |||
| − | |||
| class="sshl_f" | | | class="sshl_f" | | ||
|} | |} | ||
| − | + | =SUBTOTAL(4,A1:A6) : Calculates the subtotal of numbers in the range A1 to A6 using MAX function. Returns '''80''' as a result. | |
| − | - | + | =SUBTOTAL(5,A1:A6) : Calculates the subtotal of numbers in the range A1 to A6 using MIN function. Returns '''-2''' as a result. |
| − | + | =SUBTOTAL(9,A1:A6) : Calculates the subtotal of numbers in the range A1 to A6 using SUM function. Returns '''209''' as a result. | |
| + | =SUBTOTAL(1,A1:A6) : Calculates the subtotal of numbers in the range A1 to A6 using AVERAGE function. Returns '''34.833333333333336''' as a result. | ||
| + | |||
| + | ==See Also== | ||
| + | |||
| + | *[[Manuals/calci/AVERAGE | AVERAGE]] | ||
| + | |||
| + | |||
| + | ==References== | ||
| + | *[http://en.wikipedia.org/wiki/Aggregate_function Aggregate Functions] | ||
Revision as of 15:16, 2 April 2014
SUBTOTAL(SomeType, SomeArray)
where,
- specifies which function type to use within a list,
- is set of values or range of cells containing values.
specifies which function type to use within a list,
is set of values or range of cells containing values.SUBTOTAL() calculates a subtotal in a list or database.
Description
SUBTOTAL(SomeType, SomeArray)
- SUBTOTAL() function is designed for columns of data or vertical range of cells.
- Argument specifies which function to use in calculating subtotal from the list of functions. can be any number between 1 to 11 (that includes hidden values) or 101 to 111 (that ignores hidden values).
- Below table describes the Function Type and the number associated with it.
| Function Type | Function Number(includes hidden values) | Function Number(ignores hidden values) |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
- is an array of values or reference to cells containing values, for which the subtotal is to be calculated.
- Text values or empty cells are ignored.
- For invalid argument values, Calci displays an error message.
Examples
| 40 | |
| 50 | |
| 25.5 | |
| 80 | |
| 15.5 | |
| -2 |
=SUBTOTAL(4,A1:A6) : Calculates the subtotal of numbers in the range A1 to A6 using MAX function. Returns 80 as a result. =SUBTOTAL(5,A1:A6) : Calculates the subtotal of numbers in the range A1 to A6 using MIN function. Returns -2 as a result. =SUBTOTAL(9,A1:A6) : Calculates the subtotal of numbers in the range A1 to A6 using SUM function. Returns 209 as a result. =SUBTOTAL(1,A1:A6) : Calculates the subtotal of numbers in the range A1 to A6 using AVERAGE function. Returns 34.833333333333336 as a result.
See Also