Excel functions
Description
These are Excel functions that can be used in the script's TGKML syntax. They are usually used to manipulate the content of a variable.
Example
var := LEFT($Entity.code, 1);
- Saves, in the "var" variable, the first character of the entity code specified in the filter
Logical functions
| Element |
Description |
| AND(condition1, condition2...) |
AND logic |
| OR(condition1, condition2...) |
OR logic |
| NOT(condition) |
Negation |
| IF(condition, Truevalue, Falsevalue) |
Conditionlogic |
Aggregate functions
| Element |
Description |
| SUM(argument1, argument2, ...) |
Sum |
| AVERAGE(argument1, argument2, ...) |
Average value |
| MAX(argument1, argument2, ...) |
Maximum value |
| MIN(argument1, argument2, ...) |
Minimum value |
| COUNT(argument1, argument2, ...) |
Number of elements contained |
| PRODUCT(argument1, argument2, ...) |
Value produced by multiplying the arguments |
| SUBTOTAL (function, argument1, argument2, ...) |
Only supported in row/column/sheet formulas. Supports the following functions: - 1 - arithmetic mean - 2 - numbers count - 4 - max - 5 - min - 6 - product - 9 - sum Sum of specified arguments |
Other arithmetic functions
| Element |
Description |
| SIGN(value) |
- Returns -1 for positive values, 1 for negative values |
| ABS(value) |
Absolute value |
| MOD(dividend, divisor) |
Rest of the division |
| INT(value) |
Rounding down |
| ROUND(value, figure) |
- Rounding |
String manipulation functions
| Element |
Description |
| CONCATENATE(argument1, argument2, ...) |
Concatenation of strings. Equivalent to "String 1" + "String 2" + "String 3" ... |
| FIND(text, string, [start]) |
- Excel's "Find" function |
| LEFT(text, numberCharacters) |
- Returns the number of characters indicated starting from the start of the string. |
| LEN(text) |
Returns the length of the string. |
| LOWER(text) |
Changes the text to lowercase. |
| UPPER(text) |
Changes the text to uppercase |
| MID(text, start, numberCharacters) |
Excel's "Extract String" function |
| SUBSTITUTE(text, occurrence, substitution) |
- Excel's "Substitute" function |
| REPLACE(text, start, numberCharacters, substitution) |
Excel's "Replace" function |
| RIGHT(text, numberCharacters) |
- Returns the number of characters indicated starting from the end of the string. |
| REPT(text, times) |
Excel's Repeat function |
| TRIM(text) |
Excel's "Delete spaces" function |
| SEARCH(text, string, [start]) |
Excel's "Find" function (without distinction between uppercase and lowercase) |
| VALUE(text) |
Changes the specified text into numeric text. Excel's "Value" function |
Error management functions
| Element |
Description |
| ERR() |
Error expression |
| IFERROR(expression, value) |
Returns the processed value if the expression is an error expression, otherwise it returns the expression itself. |
| ISERROR(expression) |
Returns 1 if the expression is an error expression, otherwise it returns 0. |