Difference between revisions of "Manuals/calci/SUBTOTAL"
Jump to navigation
Jump to search
| (5 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | =SUBTOTAL( | + | <div style="font-size:30px">'''SUBTOTAL (Type,Arrays)'''</div><br/> |
| − | |||
where, | where, | ||
| − | *<math> | + | *<math>Type</math> specifies which function type to use within a list, |
| − | *<math> | + | *<math>Arrays</math> is set of values or range of cells containing values. |
| − | + | **SUBTOTAL(), returns a subtotal in a list or database. | |
| − | SUBTOTAL() | ||
== Description == | == Description == | ||
| − | SUBTOTAL( | + | SUBTOTAL(Type, Arrays) |
*SUBTOTAL() function is designed for columns of data or vertical range of cells. | *SUBTOTAL() function is designed for columns of data or vertical range of cells. | ||
| − | *Argument <math> | + | *Argument <math>Type</math> specifies which function to use in calculating subtotal from the list of functions. <math>Type</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. | *Below table describes the Function Type and the number associated with it. | ||
{| class="wikitable" | {| class="wikitable" | ||
| Line 41: | Line 39: | ||
|} | |} | ||
| − | *<math> | + | *<math>Arrays</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. | *Text values or empty cells are ignored. | ||
*For invalid argument values, Calci displays an error message. | *For invalid argument values, Calci displays an error message. | ||
| Line 79: | Line 77: | ||
=SUBTOTAL(9,A1:A6) : Calculates the subtotal of numbers in the range A1 to A6 using SUM function. Returns '''209''' 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. | =SUBTOTAL(1,A1:A6) : Calculates the subtotal of numbers in the range A1 to A6 using AVERAGE function. Returns '''34.833333333333336''' as a result. | ||
| + | |||
| + | ==Related Videos== | ||
| + | |||
| + | {{#ev:youtube|H_cHbbVwvPQ|280|center|SUBTOTAL}} | ||
==See Also== | ==See Also== | ||
*[[Manuals/calci/AVERAGE | AVERAGE]] | *[[Manuals/calci/AVERAGE | AVERAGE]] | ||
| − | |||
==References== | ==References== | ||
*[http://en.wikipedia.org/wiki/Aggregate_function Aggregate Functions] | *[http://en.wikipedia.org/wiki/Aggregate_function Aggregate Functions] | ||
| + | |||
| + | |||
| + | |||
| + | |||
| + | *[[Z_API_Functions | List of Main Z Functions]] | ||
| + | |||
| + | *[[ Z3 | Z3 home ]] | ||
Latest revision as of 16:43, 21 August 2018
SUBTOTAL (Type,Arrays)
where,
- specifies which function type to use within a list,
- is set of values or range of cells containing values.
- SUBTOTAL(), returns a subtotal in a list or database.
specifies which function type to use within a list,
is set of values or range of cells containing values.
Description
SUBTOTAL(Type, Arrays)
- 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.
Related Videos
See Also
References