Page Comparison

When you copy and paste cells that contain formulas, you can control how those formulas behave. In the majority of cases, the cell references are relative by default but you can switch to absolute references, if required. See the Copy and paste formulas (relative v absolute references) section below.

Your IT Computer Costs are a function of wages. In the formula below, the IT costs are 2% of AU wages.

You added a Manual Entry Driver tab (which is named Assumptions) in which you entered an AUD exchange rate. You want to use that rate to calculate the budgeted cost of the Australian Conference. In the formula below, the Conference - AUD value is multiplied by the exchange rate in the Assumptions tab.

You want the Sales budget values for this year to be ten percent higher than last year’s actuals. In the formula below, the April 2021 sales value is made up of the actual value from 2021 plus ten percent.

Function: ABS(value)

Description:

• 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 of which to return the absolute value.

Example: ABS(-2) = 2

Function: AND(test1, [test2…])

Description:

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

Function: AVERAGE(value1, [value2...])

Description:

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

Function: CEILING(number, significance)

Description:

• 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

Function: DAYS

Description:

• Number of days in period (e.g. June) or period range (e.g. Jul-Dec). Includes 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.

Example 3: Watch this video to learn how to use the days functions to calculate the number of days in a specified date range.

Function: FINITE(value, result)

Description:

• Checks whether a value is finite, NaN (not a number), -infinity or +infinity and returns a value corresponding to the first matching argument.

• You can also define different values for NaN, -infinity and +infinity: FINITE(value, NaN, -infinity, +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

FINITE(1/0, 5) = 5

Function: FV(rate, nper, pmt, [pv], [type])

Description:

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

Function: IF(logical expression, value if true, value if false)

Description:

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

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.

Example 3 - Set a rebate level based on the value of sales:

Function: IFERROR(value, value_if_error)

Description:

• 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 It allows you to use an if-then-else logic type of logic when entering formulas and is useful for identifying errors in a formula.

• Not It is not to be confused with the ISERROR function (see below). IFERROR is effectively a shortcut for using the IF and ISERROR funcitons 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).

Function: IPMT(rate, per, nper, pv, [fv], [type])

Description:

• 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

Function: MIN(value1, [value2...])

Description:

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

Function: MAX(value1, [value2...])

Description

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

Function: NOT(test1, [test2…])

Description:

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

Function: OR(test1, test2, [test3]...)

Description:

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

Function: POWER(base, exponent)

Description:

• Returns a number raised to a power.

• base = The number to raise to the exponent power.

• exponent = The exponent to raise base to.

Example:

POWER(5,2) The number returned when 5 is raised to the power of 2 is 25.

Function: PMT(rate, nper, pv, [fv], [type])

Description:

• 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

Function:ROUND(value, places)

Description:

• 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:

• 2.558 rounded to 1 decimal place is 2.6.

  

• 1234 rounded to -3 decimal places is 1000.

  

Function: ROUNDUP(number, num_digits)

Description:

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

$4.42 rounded up to zero decimal places is $5.00.

Function: ROUNDDOWN(number, num_digits)

Description:

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

$4.42 rounded down to zero decimal places is $4.00.

Description:

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

Example 5 - Select cells in a range (multiple methods): Watch this video…

Function: SQRT(value)

Description:

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

Function: SWITCH(expression, case1, value1, [case2, value2, ...], [default])

Description:

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

In cell A1, if you enter AUD, the result will show 1.2 but if you enter GBP, the result will be 1.6. In all other cases, the result will be 1.

Function: WEEKDAYS

Description:

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

Function: WORKINGDAYS(id, columns)

Description:

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

• You can type ‘wd’ as a shortcut for the working days function.

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.

In the image below, the formula uses a function that references a range of cells. That formula was copied forward into the other cells in the row. By default, relative referencing applies. When you press F4 to switch to absolute referencing, the reference to the first cell in the range becomes absolute, as illustrated in the second image below. Note the difference in the values in the subsequent cells.

If you press F4 again (2nd time), all the references become absolute, as illustrated in the image below.

If you press F4 again (3rd time), the reference to the other cells in the range become absolute, as illustrated in the image below.

If you press F4 again (4th time), you return to how you started; relative referencing applies again. So you can quickly switch between the different types of referencing to control how the values are copied into the other cells.