In this article:

All | Text | Date | Number | Aggregate | Filters | Lookups | Period | Queries | Math | System | Financial | Conditional | Common | Special

All | Text | Date | Number | Aggregate | Filters | Lookups | Period | Queries | Math | System | Financial | Conditional | Common | Special

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.

✭ Tips:

- Each user can individually decide how numbers are displayed in reports and models, by
selecting the desired number format in
**Manage - Profile**.

Returns the absolute value (i.e. the modulus) of a given number.

ABS(data)

Example:

data = -100.23

ABS(data) = 100.23

Returns the given number rounded up, away from zero, to the nearest integer number.

CEILING(data)

Example:

data = 2.5

CEILING(data) = 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") = 160.9344

Calculates the division of 2 amounts, and optionally rounds the result to the given
*decimals*. If a division by zero occurs, the result is returned as 0.

This
function is essentially equivalent to **IF(base=0, 0, ROUND(data/base, decimals))**
but easier to use.

DIVIDE(data, base, decimals)

Example:

DIVIDE(80, 100) = 0.8

Rounds the given number up to the nearest even integer number.

EVEN(data)

Example:

data = 1.5

EVEN(data) = 2

Rounds the given number to the given *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) = "1.50"

Returns the given number rounded down, towards zero, to the nearest integer number.

FLOOR(data)

Example:

data = 2.5

FLOOR(data) = 2

Rounds the given number downwards to the next integer number.

INT(data)

Example:

data = 100.23

INT(data) = 100

Rounds the given number upwards to the given *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

Returns an array of negative numbers in the given cell range or data array. Non-numeric values are ignored. You can use this function in combination with other functions, for example to calculate the sum or average of negative values only.

NEGATIVE(data, data, ..)

Example:

NEGATIVE(A1:A3)

SUM(NEGATIVE(A1:A3))

Returns an array of non-zero numbers in the given cell range or data array. Non-numeric values are ignored. You can use this function in combination with other functions, for example to calculate the average of non-zero values only.

NONZERO(data, data, ..)

Example:

NONZERO(A1:A3)

AVG(NONZERO(A1:A3))

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

Returns an array of numbers from the given start for the given *number* of increments. The
returned data has the same structure as a cell range or data array, which enables you to pass
this data directly into a dropdown editor or other functions that expect a cell range or data
array. You can optionally pass an *interval* (default is 1). The interval can be positive
or negative.

You can also use a text as *data*, in which case it will be suffixed by a numeric series.

NUMRANGE(data, number, interval)

Example:

NUMRANGE(5, 4) = 5, 6, 7, 8

NUMRANGE(5, 4, 10) = 5, 15, 25,
35

NUMRANGE("col", 4) = "col0", "col1", "col2", "col3"

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 %, and
optionally rounds the result to the given *decimals*. If you don't specify the decimals,
the result will be rounded to 4 decimals.

This function is essentially equivalent to
**IF(base=0, 0, ROUND(data/base, decimals))** but easier to use.

This function will return **∞ %** if the resulting percentage is larger than 1000%
(plus or minus), or if the base is zero.

PERCENT(data, base, type, decimals)

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

Returns an array of positive numbers in the given cell range or data array. Non-numeric values are ignored. You can use this function in combination with other functions, for example to calculate the sum or average of positive values only.

POSITIVE(data, data, ..)

Example:

POSITIVE(A1:A3)

SUM(POSITIVE(A1:A3))

Returns an array of the numbers in the given cell range or data array, with their sign reversed. Non-numeric values are ignored. You can use this function in combination with other functions, for example to show negative numbers on a positive scale in sparklines.

REVERSE(data, data, ..)

Example:

REVERSE(A1:A3)

SPARKLINE(REVERSE(A1:A3))

Returns the given number converted to roman, in text. Valid for numbers between 0 and 10000.

ROMAN(data)

Example:

ROMAN(499) returns CDXCIX

ROMAN(2019) returns MMXIX

Rounds the given number to the given *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 given *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 given *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 given *decimals*.

TRUNC(data, decimals)

Example:

data = 100.23

TRUNC(data) returns 100