Function

Explanation

ABS(n)

This results in the absolute value of the number n (The sign is removed.) – ABS(3) results in 3; ABS(-3) also results in 3.

ASC(s)

The position of the character s in the character set – ASC("A") results in 65.

AT(s1;s2)

This returns as a numerical value the position of the string s1 at which the string s2 occurs for the first time – AT("London";"on") returns 2.

AVG(n1;n2)

This calculates the average value of the cells with the coordinates n1 (upper left corner) and n2 (lower right corner). The coordinates must be preceded by # signs. Example: AVG (#B2;#C5). For more information, see also Table cells in calculations.

CHR(n)

The character with the position n in the character set – CHR(65) results in an "A".

COUNT(n1;n2)

This returns the number of numeric values in the cells with the coordinates n1 (upper left corner) and n2 (lower right corner). Only numbers are counted – empty cells and cells containing text are not counted. The coordinates must be preceded by # signs. Example: COUNT (#B2;#C5). For more information, see also Table cells in calculations.

DAY(d)

The day of the date d (as number) – DAY("25.09.18") results in 25.

DTON(d)

This converts the date d into a serial day number. It is needed to calculate the difference between two dates in days: DTON ("12.11.18") - DTON ("20.10.18") results in 23 (days).

IF

IF(condition;value1;value2) returns value1 if the condition is true; otherwise it returns value2. However,value2 can also be omitted. Example: IF (GENDER="F"; "Dear Ms."; "Dear Mr."). For more information, see Conditional text. Value1 and value2 can be of any type (thus numeric, for example).

INT(n)

This truncates the decimal points of the number n (without rounding) – the formula INT(3.90) would result in 3.

LEFT(s;n)

This returns the first n characters of the string s – LEFT("Write"; 4) returns "Text".

LEN(s)

Length of the string s – LEN("John") results in 4.

LOWER(s)

This converts the string s to lower case – LOWER("John") results in "john".

LTRIM(t)

This removes all leading spaces from the string s – LTRIM("   Text   ") results in "Text   ".

MAX(n1;n2)

This returns the maximum value of the cells with the coordinates n1 (upper left corner) to n2 (lower right corner) of a table. The coordinates must be preceded by a # sign. Example: MAX (#B2;#C5). For more information, see also Table cells in calculations.

MIN(n1;n2)

This returns the minimum value of the cells with the coordinates n1 (upper left corner) to n2 (lower right corner) of a table. The coordinates must be preceded by a # sign. Example: MIN (#B2;#C5). For more information, see also Table cells in calculations.

MONTH(d)

The month of the date d (as a number) – MONTH("25.09.18") results in 9.

NTOD(n)

This is the counterpart to DTON: You specify the serial day number with n and NTOD converts it into a date.

POW(n1;n2)

This returns the result of raising the number n1 to the power of n2 – POW(2;8) equals 256.

PROD(n1;n2)

This calculates the product of the cells with the coordinates n1 (upper left corner) to n2 (lower right corner) of a table. The coordinates must be preceded by a # sign. Example: PROD (#B2;#C5). For more information, see also Table cells in calculations.

RIGHT(s;n)

This returns the last n characters of the string s – RIGHT("Write"; 5) results in "Maker".

ROUND(n1;n2)

This returns the number n1 rounded to n2 decimal places – ROUND(2.44;1) equals 2.4 and ROUND(2.45;1) equals 2.5.

RTRIM(s)

This removes all trailing spaces from the string s – RTRIM("   Text   ") returns "   Text".

SQRT(n)

This returns the square root of the number n – SQRT(4) equals 2.

STR(n)

This converts the number n into a string – STR(17) would result in the string "17".

STRING(s;n)

String s is repeated n times – STRING("Text"; 3) results in "TextTextText".

SUBSTR(s;n1;n2)

This extracts n2 characters from the string s from position n1 – SUBSTR("Potato";2;3) results in "ota".

SUM(n1;n2)

This represents the sum of the cells with the coordinates n1 (upper left corner) and n2 (lower right corner) of a table. The coordinates must be preceded by a # sign. Example: SUM (#B2;#B5). For more information, see also Table cells in calculations.

TODAY()

This returns today's date in the form MM/DD/YY – the empty pair of parentheses must be entered after the function name.

TRIM(s)

This removes all spaces at the beginning and end of the string s – TRIM("   Text   ") returns "Text".

TRUNC(n1;n2)

This returns the number n1 truncated to n2 decimal places (without rounding) – TRUNC(1.2345;2) results in 1.23.

UPPER(s)

This converts the string s to uppercase letters – UPPER("John") results in "JOHN".

USER()

This returns the name under which the current user is logged into this system. The empty pair of parentheses must be entered after the function name.

VAL(s)

This converts the string s to a number. The string may also contain text; Write then takes the first number that it can find in it: VAL("9") results in 9. VAL("9 sausages") and VAL("Can I have 9 sausages and 3 rolls please?") also result in 9. VAL("sausages") results in 0, because no number is included.

YEAR(d)

The year of a date as a number (two digits) – the formula YEAR("09/17/18") results in 18.