CIRCULAR
Description
CIRCULAR(timestamp, result, add_value, minus_value, vertical_expansion)
Input | timestamp: timestamp data. Type: cell. result: output of the function. Type: cell.
add_value: value to be incremented. Type: cell.
minus_value: value to be subtracted. Type: cell.
vertical_expansion: A parameter to tell the function whether the data column in the above cells expand vertically. The value can be true or false. If not specified, the default is true. Type: logical value. |
Output | The surplus as the result of adding values and subtracting values. The computation omits the values to be added and subtracted in current row/column. That is to say, the result is the surplus of previous row/column. Type: number. |
Examples
Design a template.
Input CIRCULAR(A3, B3, C3, D3) in B3.
When previewing the effect, you will get:
The effect equals to inputing IF(&A3 = 1, 0, B3[A3:-1] + C3[A3:-1] - D3[A3:-1]) in B3
CROSSLAYERTOTAL
Description
CROSSLAYERTOTAL(group, number, value, result, vertial_expansion)
Input | group: group name.Type: cell.
number: the number in a group. Type: cell.
value: the value to sum. Type: cell.
result: output of tput of the function. Type: cell.
vertical_expansion: A parameter to tell the function whether the data column in the above cells expand vertically. The value can be true or false. If not specified, the default is true. Type: logical value. |
|---|---|
Output | The running total of values. When calculate the running total in the next group, the sum of current group is not reset. Type: number. |
Examples
Design a template.
Input CROSSLAYERTOTAL(A3, B3, C3, D3,true) in B3.
When previewing the effect, you will get:
The effect equals to inputing IF(&B3=1,C3+D3[A3:-1,B3:!-1],C3+D3[B3:-1]) in D3
HIERARCHY
Description
HIERARCHY(cell_1, cell_2,...)
Input | cell_1: the parent cell of cell_2.
cell_2: the cell containing values to be positioned. If not specified, cell_2 equals to cell_1. Type: cell. |
|---|---|
Output | Extract the value in cell_2 whose corresponding value in cell_1 is the previous one. Type: all values supported in data columns and arrays. |
Design a template.
Input HIERARCHY(A3) in C3 and HIERARCHY(A3,B3) in D3.
When previewing the effect, you will get:
The effect equals to inputing A3[A3:-1] in C3 and B3[A3:-1] in D3.
LAYERTOTAL
Description
LAYERTOTAL(number, value, result, vertial_expansion)
Input | number: the number in a group. Type: cell.
value: the value to sum. Type: cell.
result: output of tput of the function. Type: cell.
vertical_expansion: a parameter to tell the function whether the data column in the above cells expand vertically. The value can be true or false. If not specified, the default is true. Type: logical value. |
|---|---|
Output | The running total of values. When calculate the running total in the next group, the sum of current group is reset. Type: number. |
Examples
Design a template.
Input LAYERTOTAL(B3, C3, D3,true) in B3.
When previewing the effect, you will get:
The effect equals to inputing IF(&B3=0,C3,C3 + D3[B3:-1]) in D3
MOM
Description
MOM(month, value, step, vertical_expansion)
Input | month: month data. Type: cell.
value: the value to calculate the ratio. Type: cell.
step: a negative value whose absolute value tells whether the previous month locates at 1 row or more rows before current month.
vertical_expansion: A parameter to tell the function whether the data column in the above cells expand vertically, The value can be true or false. If not spedified, the default is true. Type: logical value. |
|---|---|
Output | Calculate the ratio by dividing the value in current month by that in previous month. Type: number. |
Design a template.
Input MOM(A3, B3, -1, true) in C3.
When previewing the effect, you will get:
The effect equals to inputing IF(&A3 > 1, B3 / B3[A3:-1],0) in C3.
PROPORTION
Description
PROPORTION(value)
Input | value: the value upon which the proportion is calculated. Type: cell. |
|---|---|
Output | The proportion of current value among all values. Type: number. |
Design a template.
Input PROPORTION(B3) in C3.
Set the style of text in C3 as percentage with two decimal fractions.
When previewing the effect, you will get:
The effect equals to inputing B3/SUM(B3[!0]) in C3.
SORT
Description
SORT(value, ascending_order)
Input | value: the value based on which the ranking is calculated. Type: cell.
ascending_order [optional]: a parameter to determine whether the ranking is calculated in ascending order. If not specified, the default is true. Type: logical value. |
|---|---|
Output | The rank of the value among all values. Type: interger. |
Design a template.
Input SORT(B3) in C3.
When previewing the effect, you will get:
The effect equals to inputing COUNT(B3[!0]{B3 > $B3}) + 1 in C3.
