Functions for use in formulas
Learn about the inbuilt functions you can use when entering formulas in the Analytics and Budgets & Forecasts modules.
Last updated
Learn about the inbuilt functions you can use when entering formulas in the Analytics and Budgets & Forecasts modules.
Last updated
This page applies to using functions in formulas in the Analytics (Flexible Variance mode) and Budgets & Forecasts modules. Some functions are only available in one of these modules.
While you can also use functions in the Financial Statements module, they work differently, so they're not included on this page.
ABS | AND | ARRAY | AVERAGE | CEILING | COALESCE | COUNT | DAYS | FINITE | FLOOR | FORECAST LINEAR | FV | IF | IFERROR | IPMT | ISERROR | MAX | MIN | NOT | OR | PERCENTILE | PERIODCOUNT | PMT | POWER | ROUND | ROUNDDOWN | ROUNDUP | SQRT | SUM | SWITCH | WEEKDAYS | WORKINGDAYS
ABS(value)
Returns the absolute value of a number. If the number is positive or zero, its absolute value is the number. If the number is negative, the minus sign is omitted.
value = The number (or column coordinates) of which to return the absolute value.
Example: ABS(-2) = 2
AND(test1, [test2…])
Determines if all conditions in a test are TRUE. If all conditions are TRUE, it returns TRUE. If any condition is FALSE, it returns FALSE. Commonly used with the IF function.
Test1, test2, ... are the conditions you want to test. Each test must result in either TRUE or FALSE.
ARRAY(value1, [value2…])
Groups multiple values into a single, ordered list for use as a parameter within other functions.
value1: The first value or column coordinate within the group. For example, '1'.
[value2...] = Optional, repeatable additional values or column coordinates within the group. For example, '2, 3'.
AVERAGE(value1, [value2...])
Returns the arithmetic mean of a series of numbers.
value1 = The first value or cell range to consider when calculating the average.
[value2...] = Optional, repeatable additional values to consider when calculating the average.
Example 1: AVERAGE(Jul 2025 .. Dec 2025) returns the average of the range from July 2025 to December 2025.
Example 2: AVERAGE1, 2, 3) returns the average of 1, 2 and 3, which is 2.
CEILING(number, significance)
Rounds a number away from zero, up if a positive significance, down if a negative significance.
Number (required) is the value you want to round.
Significance (required) is the multiple to which you want to round.
Example 1: CEILING($4.42,0.10) $4.42 rounded up to the nearest multiple of 0.10 is $4.50.
Example 2: CEILING($4.42, 1) $4.42 rounded up to the nearest multiple of 1 is 5.
Example 3: CEILING(-4.42, -2) -$4.42 rounded up to the nearest multiple of -2 is -$6.
See also ROUNDUP
COALESCE(value1, [value2…])
Returns the first non-empty value from the parameters.
Value1 is the first value or column coordinate to consider when searching for the first non-empty value. For example, []:[current].
Value2... is optional, repeatable additional values or column coordinates to consider when searching for the first non-empty value. For example, 0.
Returns the number of days in period (e.g. June) or period range (e.g. Jul-Dec), includingthe weekend days.
Useful for calculating values where days matter, such as costs that are based on the number of days in a period.
Select a cell or range of cells in between brackets, or leave blank for current period.
Example 1: DAYS(Jun 2024) returns the number of days in June 2024.
Example 2: You can refer to the other cells in the formula to calculate the number of days in a date range. Enter the function and within the parameters, select the cells that contain the values you want to include. DAYS(Jul 2025 - Dec 2025) returns the number of days in the range from July 2025 to December 2025.
Checks whether a value is finite, NaN (not a number), -infinity or +infinity and returns a value corresponding to the first matching argument (fallback).
In Budgets & Forecasts you can write the funciton in two ways:
FINITE(value, result if not finite) where:
value is the value to check for finiteness.
result if not finite is what's returned if the specified value is not finite.
FINITE(value, NaN, ‑infinity, +infinity) where you define different values for NaN, ‑infinity and +infinity.
In Analytics, you write the function as: FINITE(value, fallback, [-infinity], [+infinity]) where:
value is the value or column coordinate to check for finiteness. For example: []:[current].
fallback is what's returned for all non-finite values if 2 parameters are provided, or for NaN values if 4 parameters are provided.
[‑infinity] is what's returned for ‑infinity.
[+infinity] is what's returned for +infinity.
Example 1: A common use case is to return a specific value of a formula that is divided by zero, for example, by returning a zero value rather than the #DIV/0! reference. FINITE(1/0, 0) returns the specified value of 0 instead of the #DIV/0! reference.
Example 2: FINITE(-1/0,1,2,3) returns the value 2 as that was what was defined for -infinity, instead of the #DIV/0! reference.
Other examples: FINITE(NaN, 0) = 0 and FINITE(1/0, 5) = 5
FLOOR(number, significance)
Rounds a number down towards zero to the nearest number of significance.
Number (required) is the value you want to round.
Significance (required) is the multiple to which you want to round.
Example 1: FLOOR($4.42,0.10) $4.42 rounded down to the nearest multiple of 0.10 is $4.40.
Example 2: FLOOR($4.42, 1) $4.42 rounded down to the nearest multiple of 1 is 4.
Example 3: FLOOR(-4.42, -2) -$4.42 rounded down to the nearest multiple of -2 is -$6.
See also ROUNDUP
FV(rate, nper, pmt, [pv], [type])
Returns the future value of a loan based on a constant interest rate and repayments.
Rate (required) is the interest rate for the loan.
Nper (required) is the total number of payments for the loan.
Pmt or PV (required):
Pmt is the payment made each period. This typically contains principal and interest. If omitted, you must include the pv argument.
Pv is the present value or total amount a series of future payments is worth now. If omitted, you must include the pmt argument.
Type (optional) indicates when payments are due, either 0 or 1. If 0 or omitted, payments are due at the end of the period. If 1, payments are due at the beginning of the period.
Use consistent units for specifying the rate and nper. For example, if you make monthly payments at an annual interest rate of 5% on a 2-year loan, use 5%/12 for the rate and 2*12 for the nper. If you make annual payments, use 5% for rate and 2 for nper.
Example: FV(0.06, 8, 1000) returns -$9897.47, which is the future value of an 8-year loan with repayments of $1,000 per year at an annual interest rate of 6%.
See also PMT.
IF(logical expression, value if true, value if false)
Returns a value depending on logical expression.
A logical expression is a statement that evaluates to either TRUE or FALSE.
value if true is the value that is returned if the logical expression is TRUE.
value if false is the value that is returned if the logical expression is FALSE.
Allows you to use an if-then-else type of logic when entering formulas. Useful for setting thresholds for various parts of a model, such as setting a discount level based on the value of sales. For example, when monthly sales are greater than 7 million, you receive a rebate of 5% and when it is below 7 million, you will not receive a rebate.
Example 1 (basic IF function): IF(Sales > 7000000, Sales * 0.05, 0) means that if Sales are greater than 7 million, 5% of the Sales will be returned; else 0 will be returned.
Example 2 (nested IF function): If Sales are greater than 7 million, 5% of the Sales will be returned; else, if Sales are greater than 1.2 million, 2% of the Sales will be returned, else 0 will be returned.
See also IFERROR.
IFERROR(value, value_if_error)
Returns the value of a specified calculation unless that calculation results in an error, in which case it returns a specified value instead.
value is the calculation for which you want to view the result.
value_if_error is the value that is returned if the calculation results in an error.
Allows you to use an if-then-else logic type when entering formulas and is useful for identifying errors in a formula.
Not to be confused with the ISERROR function (see below). IFERROR is effectively a shortcut for using the IF and ISERROR functions together.
Example 1: IFERROR(10/5,0) returns 2 because the calculation (10/5) doesn’t evaluate to an error.
Example 2: IFERROR(10/0,0) returns 0 because the calculation returns an error (#DIV/0).
See also IF and ISERROR.
ISERROR(value)
Determines if a value is an error or not. If the value is an error, it returns TRUE. If the value isn’t an error, it returns FALSE.
value is the value that you want to test.
Allows you to use an if-then-else logic type when entering formulas and is useful for identifying errors in a formula.
Not to be confused with the IFERROR function (see above).
Example 1: ISERROR(10/5,0) returns FALSE because the calculation (10/5) doesn’t evaluate to an error.
Example 2: ISERROR(#DIV/0) returns TRUE because #DIV/0 returns an error.
IPMT(rate, per, nper, pv, [fv], [type])
Returns the interest payment for a loan based on constant payments and a constant interest rate.
Rate (required) is the interest rate for the loan.
Per (required) is the period for which you want to find the interest rate, which must be in the range of 1 to nper.
Nper (required) is the total number of payments for the loan.
Pv (required) is the present value or total amount a series of future payments is worth now.
Fv (optional) is the future value remaining after the final payment is made. If omitted, this is assumed to be 0.
Type (optional) indicates when payments are due, either 0 or 1. If 0 or omitted, payments are due at the end of the period. If 1, payments are due at the beginning of the period.
Use consistent units for specifying the rate and nper. For example, if you make monthly payments at an annual interest rate of 5% on a 2-year loan, use 5%/12 for the rate and 2*12 for the nper. If you make annual payments, use 5% for rate and 2 for nper.
Example: IPMT(0.06,1,8,8000) returns -$480.00, which is the interest due in the first year of an 8-year loan of $8,000.
See also PMT.
MAX(value1, [value2...])
Returns the largest number in a set of values.
value1 = The first value or cell range to consider when calculating the maximum value.
[value2...] = Optional, repeatable additional values to consider when calculating the maximum value.
Example 1: MAX(Jul 2025 .. Dec 2025) returns whichever has the largest number out of the range from July 2025 to December 2025.
Example 2: MAX(-2, 5, 10) returns 10 as that is the largest number out of -2, 5, and 10.
MIN(value1, [value2...])
Returns the smallest number in a set of values.
value1 = The first value or cell range to consider when calculating the minimum value.
[value2...] = Optional, repeatable additional values to consider when calculating the minimum value.
Example 1: MIN(Jul 2025 .. Dec2025) returns whichever has the smallest number out of the range from July 2025 to December 2025.
Example 2: MIN(-2, 5, 10) returns -2 as that is the smallest number out of -2, 5, and 10.
NOT(test1, [test2…])
Determines if a value is different from one or more of the conditions. If the value is not any of the conditions, it returns TRUE. If the value matches one of the conditions, it returns FALSE. Commonly used with the IF function.
Test1, test2, ... are the conditions you want to test. Each test must result in either TRUE or FALSE.
OR(test1, test2, [test3]...)
Determines if any conditions in a test are TRUE. If at least one is TRUE, it returns TRUE. If all conditions are FALSE, it returns FALSE. Commonly used with the IF function.
Test1, test2, ... are the conditions you want to test. Each test must result in either TRUE or FALSE.
PERCENTILE(series, k)
Returns the kth percentile value for the given series (inclusive of k), where k is a value from 0 to 1.
Series is an array of values for which to calculate the kth percentile.
k is the value between 0 and 1 (inclusive) of the percentile rank.
PERIODCOUNT(coordinate)
Returns the number of period bands (e.g. months, days) in the period associated with a column.
Coordinate is the coordinate of a transaction column to find the period for.
For example, if the []:[current] column references the Rolling 12 Months period, PERIODCOUNT([]:[current]) will return 12.
PMT(rate, nper, pv, [fv], [type])
Returns the payment for a loan based on constant payments and a constant interest rate. This includes the principal and interest.
Rate (required) is the interest rate for the loan.
Nper (required) is the total number of payments for the loan.
Pv (required) is the present value or total amount a series of future payments is worth now.
Fv (optional) is the future value remaining after the final payment is made. If omitted, this is assumed to be 0.
Type (optional) indicates when payments are due, either 0 or 1. If 0 or omitted, payments are due at the end of the period. If 1, payments are due at the beginning of the period.
Use consistent units for specifying the rate and nper. For example, if you make monthly payments at an annual interest rate of 5% on a 2-year loan, use 5%/12 for the rate and 2*12 for the nper. If you make annual payments, use 5% for rate and 2 for nper.
Example: PMT(6, 8, 8000) returns -$1,288.29, which is the payment for an 8-year loan of $8,000 at an annual interest rate of 6%.
See also IPMT.
POWER(base, exponent)
Returns a number raised to a power.
base = The number to raise to the exponent power.
exponent = The exponent to raise the base to.
Example: POWER(5,2) The number returned when 5 is raised to the power of 2 is 25.
ROUND(value, places)
Rounds a number to a specified number of decimal places.
value = The value or cell to round.
places = The number of decimal places to round to. Can be positive or negative. If using negative place values it can round to 10s, 100s, etc.
The returned value, when displayed in the grid, will display rounded to match the rounding of the cell that the formula is entered into. However, the underlying value will be accurate to the decimal places you specify and can be published, or referenced, by other cells.
Examples:
ROUND(2.558, 1) is 2.558 rounded to 1 decimal place, which is 2.6.
ROUND(1234, -3) is 1234 rounded to -3 decimal places is 1000.
ROUNDDOWN(number, num_digits)
Rounds a number down to a specified number of places.
Number (required) is any real number that you want to round down.
Num_digits (required) is the number of decimal places to which you want to round the number.
Example: ROUNDdown($4.42,0) is $4.42 rounded down to zero decimal places, which is $4.00.
ROUNDUP(number, num_digits)
Rounds a number up to a specified number of places.
Number (required) is any real number that you want to round up.
Num_digits (required) is the number of decimal places to which you want to round the number.
Example: ROUNDUP($4.42,0) is $4.42 rounded up to zero decimal places, which is $5.00.
SQRT(value)
Returns the positive square root of a positive number.
value = The number for which to calculate the positive square root.
Example: SQRT(4) returns the square root of 4 is 2.
SUM(value1, [value2…])
Returns the sum of a series of numbers, cell ranges and/or cells.
value1 = The first value or cell range to consider when calculating the sum.
value2 = Optional, repeatable additional values to consider when calculating the sum.
You can use commas or a colon to select the arguments:
SUM(value1, [value2…]) where value 1 is the first value and value 2… are the repeatable additional values to consider when calculating the sum.
SUM([value1:valueX…]) where value 1 is the first value and value X is the last value in the range of cells to consider when calculating the sum.
Example 1: SUM(Jul 2025 .. Dec 2025) returns the sum of the range from July 2025 to December 2025.
Example 2: SUM(Jun 2023, Jul 2023) returns the sum of the June 2023 and the July 2023 cells.
Example 3: To select cells in a range one at a time, use a comma between each selected value, such as SUM(1,2,3) = 6. In the following example, the value in cell FL > Sep 2002 is the sum of the values in FL > Oct 2022, FL > Nov 2022 and FL > Dec 2022. In other words, 881 = 421 + 60 + 400.
Example 4: To select a range of cells using keyboard shortcuts, use your keyboard to select a range of cells. Select the first cell in the range then either:
Hold the Shift+Arrow keys to select the other cells one at a time.
Hold the Shift key and select the last cell in the range.
In the following example, the value in cell FL > Sep 2002 is the sum of the values in FL > Oct 2022 : Dec 2022.
SWITCH(expression, case1, value1, [case2, value2, ...], [default])
Suitable when dealing with multiple currencies, where you want the FX rate to differ depending on whether you write AUD, GBP, or USD.
Expression (required) is the reference cell.
Case1 (required) is the first test to be checked against the reference.
Value1 (required) is the corresponding result if the test is true (i.e. the expression matches the case).
Example: SWITCH(A1, “AUD”, 1.2, “GBP”, 1.6, 1.0) means in cell A1, if you enter AUD, the result will be 1.2 but if you enter GBP, the result will be 1.6. In all other cases, the result will be 1.
WEEKDAYS
The number of days in period (e.g. June) or period range (e.g. Jul-Dec). Includes weekend days.
Select a cell or range of cells in between brackets, or leave blank for current period.
Example 1: WEEKDAYS(Jun 2024) returns the number of weekdays in June 2024.
Example 2: WEEKDAYS(Jul 2025 - Dec 2025) returns the number of weekdays in the range from July 2025 to December 2025.
See also DAYS.
WORKINGDAYS(id, columns)
Tip: Type ‘wd’ as a shortcut for accessing this function.
Returns the number of working days in period (e.g. June) or period range (e.g. Jul-Dec) as defined by the specified working days calendar.
id = Calendar Id to fetch information from.
column = Optional, column or column range. Select a cell or range of cells in between brackets, or leave blank for current period. Note: In Analytics, the column is used for the argument rather than the period because the period associated with a column can change. For example, you can use the Period menu to change from the Last 3 months to Rolling 12 months period. When you change the period, the number of days in that period changes accordingly.
Example 1: WORKINGDAYS(Working Days UK, Jun 2024) returns the number of working days in the month of June 2024 based on the Working Days UK calendar.
Example 2: WORKINGDAYS(Working Days UK, Jul 2025 - Dec 2025) returns the number of working days in the range from July 2025 to December 2025 based on the Working Days UK calendar.
Example 3 (steps):
Select the WORKINGDAYS function. A list of available calendars displays.
Select the required calendar from the list, then select the column from which to get the period range.
See also DAYS.
Example 5: for a demonstration of how to select cells in a range (multiple methods).
Press Enter. The grid updates to display the number of working days in the period in the calculated column.
