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 14: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.
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