Mathematical functions

Functions for aggregating values and for basic arithmetic. ROUND, CEIL, FLOOR, RAND and RANDINT are on Rounding and random numbers. Operators such as +, -, * and / are on Operators; there is no % or ** operator — use MOD and POWER.

MIN#

MIN returns the smallest of the given values and skips unknown values (Null and NaN).

1
2

MIN(value1, value2, ...)
MIN(collection)

Parameters — 1 to 100 values, or exactly one collection. The values do not have to be numbers, but they must be comparable with each other, for example datetime values.

Returns — the smallest known value. When no value is known, the result is NaN (Null when every input is Null).

Example

MIN(40, 80, NaN);
# Result: 40
MIN({1, NaN, 3});
# Result: 1
VAR d1 := DATETIME(2014, 12, 8, 0, 0, 0, 0, DateTimeKind.Utc);
VAR d2 := DATETIME(2015, 12, 8, 0, 0, 0, 0, DateTimeKind.Utc);
MIN(d1, d2);
# Result: the value of d1

Errors — Collection is empty for an empty collection.

MINSTRICT#

MINSTRICT returns the smallest of the given values, but as soon as it meets an unknown value (Null or NaN) it returns that value instead — use it when one missing input must invalidate the whole result.

1
2

MINSTRICT(value1, value2, ...)
MINSTRICT(collection)

Example

MINSTRICT(40, 80);
# Result: 40
MINSTRICT(80, NaN);
# Result: NaN
MINSTRICT({1, NaN, 3});
# Result: NaN

Arguments and errors are the same as for MIN.

MAX#

MAX returns the largest of the given values and skips unknown values (Null and NaN); it accepts the same arguments as MIN.

1
2

MAX(value1, value2, ...)
MAX(collection)

Example

MAX(40, 80, NaN);
# Result: 80
MAX({1, NaN, 3});
# Result: 3

MAXSTRICT#

MAXSTRICT returns the largest of the given values, or the first unknown value (Null or NaN) it meets.

1
2

MAXSTRICT(value1, value2, ...)
MAXSTRICT(collection)

Example

MAXSTRICT(2, 2, 6);
# Result: 6
MAXSTRICT({1, NaN, 3});
# Result: NaN

AVG#

AVG returns the arithmetic mean of the given values as a double and skips Null and NaN.

1
2

AVG(value1, value2, ...)
AVG(collection)

Returns — double; NaN when no value is known. Characters are averaged by their character codes, so AVG('a', 'c') is the number 98, not the character 'b'.

Example

AVG(40, 80, NaN);
# Result: 60
AVG({1, NaN, 3});
# Result: 2
AVG('a', 'c');
# Result: 98

AVGSTRICT#

AVGSTRICT returns the arithmetic mean of the given values, or NaN as soon as one of them is NaN.

1
2

AVGSTRICT(value1, value2, ...)
AVGSTRICT(collection)

Returns — double. A Null input is a run-time error, not a skipped value.

Example

AVGSTRICT(40, 80);
# Result: 60
AVGSTRICT(80, NaN);
# Result: NaN

ABS#

ABS returns the absolute value of a number as a double.

ABS(-100);
# Result: 100

SIGN#

SIGN returns -1 for a negative number, 0 for zero and 1 for a positive number, as an int.

SIGN(-22.5);
# Result: -1

SQRT#

SQRT returns the square root of a number that is 0 or greater.

SQRT(25);
# Result: 5

Errors — Argument for SQRT cannot be less than 0.

POWER#

POWER raises a number to the given exponent; the exponent may be negative or a fraction.

1

POWER(number, exponent)

Example

POWER(2, 3);
# Result: 8
POWER(10, -3);
# Result: 0.001
POWER(25, 0.5);
# Result: 5

MOD#

MOD returns the remainder of dividing one number by another; decimal numbers work too.

1

MOD(dividend, divisor)

Returns — double with the sign of the dividend, so a negative dividend gives a negative remainder (unlike MOD in Excel). A divisor of 0 gives NaN, not an error.

Example

MOD(6, 4);
# Result: 2
MOD(6, 2.5);
# Result: 1
MOD(-7, 3);
# Result: -1

LOG#

LOG returns the logarithm of a positive number to the given base, or to base 10 when the base is left out.

1
2

LOG(number)
LOG(number, base)

Example

LOG(100);
# Result: 2
LOG(4, 0.5);
# Result: -2

Errors — Arguments for LOG cannot be less or equal to 0; Second argument for LOG cannot be less or equal to 0 or 1.

LN#

LN returns the natural logarithm (base e) of a positive number.

LN(100);
# Result: 4.605 (rounded)

Errors — Argument for LN cannot be less or equal to 0.

DEWPOINT#

DEWPOINT returns the dew point in °C from the air temperature in °C and the relative humidity given as a fraction from 0 to 1 (0.5 means 50 %).

1

DEWPOINT(temperature, relativeHumidity)

Returns — double, calculated with the Magnus formula (equation). When an argument is not a number at all (for example Null or text), the result is -40.

Example

DEWPOINT(20, 0.5);
# Result: 9.26 (rounded)
DEWPOINT(0, 1);
# Result: 0

Pass the humidity as a fraction. DEWPOINT(20, 50) returns 103.17, a meaningless value, without any error. A humidity in percent, for example a raw value read from a Modbus register, must be divided by 100 first.