You are here: Help Topics > Customization > Calculation > CGROUPT Function

CGROUPT Function

The CGROUPT function returns the total value of all cells assigned to table specific sum group. When specifying the name of the sum group, place the sum group in quotes and specify the table label of the group. The value entered as the sum group must be a constant (i.e. cannot be a formula).

The formula also allows an ignore zeros option which, when used, returns the count of the cells within the sum group (see example 3, below).

Note: Table cells must have the Table Relative option enabled to be considered part of the sum group by the CGROUPT function. To access cells with the option disabled, use the CGROUP function.

Syntax

CGROUPT("group name",<ignore zeros> )

To specify just the group name, where the cell containing the function is located within the table containing the table group.

Alternate Syntax

CGROUPT("table name|groupname ",<ignore zeros>)

To specify the table as well as the group name for the function.

Where there is a table TES with a sum group called PY1, like this:

tes.c100 = 10

tes.c101 = 0

tes.c102 = 20

tes.c103 = 100

tes.c104 = 0

tes.c105 = 150

Alternate Syntax

CGROUPT("sum group",<argument>)

If the argument is included:

Argument

0 The function returns a count of all cells in the sum group.

1 The function returns a count of cells in the sum group with non-zero value.

2 The function returns the sum of all cells in the sum group with positive values.

3 The function returns the sum of all cells in the sum group with negative values.

Example 1

CGROUPT("PY1")

Where PY1 is the name of the sum group, and the cell is within the table TES. This function returns the value of all cells assigned to the sum group PY1 in the table TES, which is 280.

Example 2

CGROUPT("TES|PY1")

Where PY1 is the name of the sum group, and TES is the table label. This function returns the value of all cells assigned to the sum group PY1 in the table TES, which is 280.

When using this syntax, instead of the syntax given in example 1, the function can be used outside of the table TES. If the table is renamed or renumbered, the formula will also be renamed or numbered.

Example 3

The function allows a second parameter to exclude 0 cells in the calculation and to return a count of the cells rather than return the sum of the values, in this way:

CGROUPT count including zeros

CGROUPT("abc", 0) returns 6 (the total number of cells in the cell group)

CGROUPT count not including zero values

CGROUPT("abc", 1) = 4 ( the number of cells in the sum group with non zero values)

Notes

  • Other functions that can use the CGROUPT function, such as AVERAGE, MEDIAN, STDDEV and TOTAL, accept the combined table name|group name parameter of the function. If the table name is omitted in these functions, however, they do not default to using the table name for the table the cell with the function is located in.

Related Topics