These functions perform an operation or calculation on numerical values, and return the result. You can use these to make calculations in imports, reports, or models.
Returns the absolute value (i.e. the modulus) of a given number.
ABS(data)
Example:
data = -100.23
ABS(data) returns 100.23
Returns the given number rounded up, away from zero, to the nearest integer number.
CEILING(data)
Example:
data = 2.5
CEILING(data) returns 3
Returns the given number, after converting it from one unit of measure to another.
CONVERT(data, from, to)
The "from" and "to" parameters need to be one of the defined units in below list (this function is fully compatible with Excel, but we have only listed the most commonly used measurement units):
Weight and mass
"g" - gram
"sg" - slug
"lbm" - pound mass
"u" - atomic mass
"ozm" - ounce mass
"grain" - grain
"lcwt" - hundredweight
"stone" - stone
"ton" - ton
Distance
"m" - meter
"mi" - statute mile
"nmi" - nautical mile
"in" - inch
"ft" - foot
"yd" - yard
Time
"yr" - year
"d" - day
"hr" - hour
"min" - minute
"sec" - second
Volume
"tsp" - teaspoon
"mtsp" - modern teaspoon
"tbs" - tablespoon
"oz" - fluid ounce
"cup" - cup
"pt" - pint
"qt" - quart
"gal" - gallon
"l" - liter
The "from" and "to" parameters can be prefixed with any of these codes:
"k" - kilo
"h" - hecto
"d" - deci
"c" - centi
"m" - milli
"u" - micro
Examples:
CONVERT(100, "mi", "km") returns 160.9344
Rounds the given number up to the nearest even integer number.
EVEN(data)
Example:
data = 1.5
EVEN(data) returns 2
Rounds the given number to the specified decimals, and returns a formatted text (as per the user settings). You can use this function if you want to represent values as formatted text (for example, when you want to concatenate it with other text).
FIXED(data, decimals)
Example:
data = 1.5
FIXED(data) returns "1.50"
Returns the given number rounded down, toward zero, to the nearest integer number.
FLOOR(data)
Example:
data = 2.5
FLOOR(data) returns 2
Rounds the given number down to the next integer number.
INT(data)
Example:
data = 100.23
INT(data) returns 100
Rounds the given number upwards to the specified multiple and decimals. If decimals is not specified, it rounds to an integer number.
MROUND(data, multiple, decimals)
Example:
data = 43
MROUND(data, 10) returns 50
Automatically recognizes different number formats, ignores non-numeric characters, and converts to numberic value. By default the . (dot) is regarded as the decimal sign, but you can specify any other character as decimal sign.
NUMBER(data, sign)
Type: a decimal character (e.g. " ", "." or
",")
Example:
data = "$100,23"
NUMBER(data, ",") returns 100.23
Rounds the given number up to the nearest odd integer number.
ODD(data)
Example:
data = 1.5
ODD(data) returns 3
Calculates the percentage of 2 amounts, either as a proportion % or as a variance %. If a division by zero occurs, the result is returned as 0. This function is equivalent to IF(base=0, 0, data/base) but simpler to use.
PERCENT(data, base, type)
Type:
0 - the proportion % of data on base (default)
1 - the variance
in % between data and base
Example:
PERCENT(80, 100) returns 0.8
PERCENT(80,
100, 1) returns -0.2
Rounds the given number to the specified decimals. If decimals is not specified, it rounds to an integer number.
ROUND(data, decimals)
Example:
data = 100.23
ROUND(data, 1) returns 100.2
Rounds the given number downwards to the specified decimals. If decimals is not specified, it rounds to an integer number.
ROUNDDOWN(data, decimals)
Example:
data = 100.29
ROUNDDOWN(data, 1) returns 100.2
Rounds the given number upwards to the specified decimals. If decimals is not specified, it rounds to an integer number.
ROUNDUP(data, decimals)
Example:
data = 100.21
ROUNDUP(data, 1) returns 100.3
Returns the sign (+1, -1 or 0) of a given number.
SIGN(data)
Example:
data = -100.23
SIGN(data) returns -1
Truncates the given number towards zero (i.e. rounds a positive number down and a negative number up), to the specified decimals.
TRUNC(data, decimals)
Example:
data = 100.23
TRUNC(data) returns 100