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

Functions for number conversion

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:

ABS

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

This function is useful for removing the effect of sign when comparing or aggregating values. It’s handy in variance analysis, profit/loss breakdowns, or measuring magnitude regardless of direction.

ABS(data)

Example:
data = -100.23
ABS(data) = 100.23

CEILING

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

This function is useful for rounding up cost estimates or sales targets to whole numbers for budget setting or presentation purposes.

CEILING(data)

Example:
data = 2.5
CEILING(data) = 3

CONVERT

Returns the given number, after converting it from one unit of measure to another.

This function is useful for converting physical measures such as volume, time, or distance, which is particularly helpful in logistics, manufacturing, or cross-border reporting.

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

DIVIDE ❖ XLReporting

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.

This function is useful for calculating ratios or metrics (e.g. return on sales), while avoiding division errors in financial models or dashboards.

DIVIDE(data, base, decimals)

Example:
DIVIDE(80, 100) = 0.8

EVEN

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

This function is useful for adjusting allocation counts or simplifying rounded values in structured reporting or scenarios requiring symmetrical splits.

EVEN(data)

Example:
data = 1.5
EVEN(data) = 2

FIXED

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).

This function is useful for preparing numbers for concatenation into labels, comments, or other narrative reporting that mixes text and numbers.

FIXED(data, decimals)

Example:
data = 1.5
FIXED(data) = "1.50"

FLOOR

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

This function is useful for enforcing conservative rounding (e.g. minimum expense thresholds or discounts) in cost planning.

FLOOR(data)

Example:
data = 2.5
FLOOR(data) = 2

INT

Rounds the given number downwards to the next integer number.

This function is useful for extracting whole units from calculations like invoice quantities, or when truncating decimal values.

INT(data)

Example:
data = 100.23
INT(data) = 100

MROUND

Rounds the given number upwards to the given multiple and decimals. If decimals is not specified, it rounds to an integer number.

This function is useful for rounding prices or quantities to standard units like packs of 10 or cost brackets (e.g. round to nearest €5).

MROUND(data, multiple, decimals)

Example:
data = 43
MROUND(data, 10) returns 50

NEGATIVE ❖ XLReporting

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.

This function is useful for isolating losses, deficits, or declines in financial results for targeted analysis.

NEGATIVE(data, data, ..)

Example:
NEGATIVE(A1:A3)
SUM(NEGATIVE(A1:A3))

NONZERO ❖ XLReporting

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.

This function is useful for excluding blanks or zeros in averages, charts, or ratio calculations, which is commonly used in benchmarking or portfolio reporting.

NONZERO(data, data, ..)

Example:
NONZERO(A1:A3)
AVG(NONZERO(A1:A3))

NUMBER

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.

This function is useful for importing and preparing messy financial data, such as values containing currency signs or localized decimal formats.

NUMBER(data, sign)

Type:
a decimal character (e.g. " ", "." or ",")

Example:
data = "$100,23"
NUMBER(data, ",") returns 100.23

NUMRANGE ❖ XLReporting

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.

This function is useful for generating index ranges, dropdown values, or labels in reporting structures or simulations.

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"

ODD

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

This function is useful for creating symmetrical allocations, adjusting tax brackets, or avoiding even splits when needed.

ODD(data)

Example:
data = 1.5
ODD(data) returns 3

PERCENT ❖ XLReporting

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.

This function is useful for quickly expressing performance metrics like growth, margin, or deviation, which is core to every financial report.

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

POSITIVE ❖ XLReporting

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.

This function is useful for isolating gains, revenues, or growth trends for focused metrics or visualizations.

POSITIVE(data, data, ..)

Example:
POSITIVE(A1:A3)
SUM(POSITIVE(A1:A3))

REVERSE ❖ XLReporting

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.

This function is useful for flipping values for presentation purposes, like showing costs as positive in sparklines or standardizing sign conventions in reports.

REVERSE(data, data, ..)

Example:
REVERSE(A1:A3)
SPARKLINE(REVERSE(A1:A3))

ROMAN

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

This function is useful for flipping values for presentation purposes, like showing costs as positive in sparklines or standardizing sign conventions in reports.

ROMAN(data)

Example:
ROMAN(499) returns CDXCIX
ROMAN(2019) returns MMXIX

ROUND

Rounds the given number to the given decimals. If decimals is not specified, it rounds to an integer number.

This function is useful for standardizing numeric values for presentation or controlling decimal precision in calculations.

ROUND(data, decimals)

Example:
data = 100.23
ROUND(data, 1) returns 100.2

ROUNDDOWN

Rounds the given number downwards to the given decimals. If decimals is not specified, it rounds to an integer number.

This function is useful for conservative budgeting or pricing strategies where rounding down avoids overstating amounts.

ROUNDDOWN(data, decimals)

Example:
data = 100.29
ROUNDDOWN(data, 1) returns 100.2

ROUNDUP

Rounds the given number upwards to the given decimals. If decimals is not specified, it rounds to an integer number.

Rounds a number up, away from zero, to a specified number of decimals.This function is useful for margin planning, risk buffers, or rounding in favor of overestimation.

ROUNDUP(data, decimals)

Example:
data = 100.21
ROUNDUP(data, 1) returns 100.3

SIGN

Returns the sign (+1, -1 or 0) of a given number.

This function is useful for testing values in models or directing flow logic in formulas (e.g. handling income vs. expense behavior).

SIGN(data)

Example:
data = -100.23
SIGN(data) returns -1

TRUNC

Truncates the given number towards zero (i.e. rounds a positive number down and a negative number up), to the given decimals.

This function is useful for testing values in models or directing flow logic in formulas (e.g. handling income vs. expense behavior).

TRUNC(data, decimals)

Example:
data = 100.23
TRUNC(data) returns 100

Recommended reading:
Back to top | Imports | Reports | Models | Filters

We value your privacy

We use cookies to enhance your browsing experience and analyze our traffic.
By accepting, you consent to our use of cookies.

Accept Reject Cookie Policy