Page Comparison

You can enter formulas into the cells in a worksheet. These formulas can reference cells in different worksheets, working rows or comparison rows. Rather than including more complex spreadsheet functions, Budgets and Forecasts keeps a level of simplicity by allowing for basic arithmetic in these formulas.

Enter a basic formula

You enter formulas in a similar way to how you do it in other spreadsheet software, such as Microsoft Excel or Google Sheets, either directly into a cell or in the fx (formula) bar at the top of the grid when the cell is selected.

It is easy to identify and understand formulas in Budgets and Forecasts. When a cell contains a formula, the cell value has a blue fx indicator next to it. You can hover over this indicator to view the formula or click a cell and view the formula in the fx bar.

Use cell references in formulas

To enter more advanced formulas, you can refer to values in other cells in the same worksheet, including those in comparison, sum and working lines, and in other worksheets (driver tabs).

When you view a formula in the fx bar, a description of the referenced cell displays, along with a colored dot corresponding to that cell. So you can easily see what you’re calculating, rather than just a reference to a cell number.

Use functions in formulas

You can use several functions during your formula entry, as listed in the following table.

Function	Amount	Example
SUM	Returns the sum of a series of numbers and/or cells.	SUM(1,2,3) = 6
SQRT	Returns the positive square root of a positive number.	SQRT(4) = 2
POWER	Returns a number raised to a power.	POWER(5,2) = 25
MIN	Returns the minimum value of a series of numbers and/or cells.	MIN(-2,5,10) = -2
MAX	Returns the maximum value of a series of numbers and/or cells.	MAX(-2,5,10) = 10
FINITE	Returns a specified value if the result of the formula with a “not a number” (NaN) reference and or an infinity value eg divide by zero. The FINITE formula includes a more complex option where a user can define different values for NaN, negative infinity and positive infinity) FINITE( value , NaN , -ve Infinity, +ve Infinity). Info A common use case is to return a specific value of a formula that is divide by zero, for example, by returning a zero value rather than the #DIV/0! reference.	FINITE(NaN, 0) = 0 FINITE(1/0, 5) = 5 FINITE(-1/0,1,2,3) = 2
AVERAGE	Returns the arithmetic mean of a series of numbers.	AVERAGE(1,2,3) = 2
ABS	Returns the absolute value of a number.	ABS(-2) = 2
IF	Returns a value based on conditional logic. 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.	IF(Sales>7000000,Sales*0.05,0]
DAYS WEEKDAYS WORKINGDAYS	Returns the certain number of days in a time period, such as a month. Useful for calculating values where days matter, such as costs that are based on the number pf days in a period. Three options are available: Number of days, for example, February has 28 days. Number of weekdays (Monday through to Friday), for example February 2023 has 20 weekdays. Number of working days based on a predefined calendar, for example, you might have a calendar for your region or one that excludes public holidays. If you do not specify a cell within the parameters, it returns the number of days, weekdays or working days, as applicable. 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. For example, the following formulas returns the number of days in the April through to June quarter. Image RemovedImage Added Tip You can type ‘wd’ as a shortcut for the working days function.	=DAYS() =WEEKDAYS() =WORKINGDAYS()

Copy and paste formulas

To save time, you can copy and paste values and this includes formulas. When you copy a formula, the pasted formula might have an absolute or relative reference, depending on the situation:

• Absolute referencing occurs when you copy forward a cell in the Total column, i.e. the formula will always reference the total.

• Relative referencing occurs in the following situations:

  • When you copy forward a cell, the formulas are pasted into the target cells are relative, i.e. if a cell references January data, when you copy the cell to February, it will reference February data.

  • When you copy a formula that references a comparison row, the formula you paste into the target cell(s) is relative. This applies both to formulas copied to the same row and to different rows from the original cell.

  • When you copy a formula that references a working row, the formula you paste into the target cell(s) is relative. For example, if the original cell has two working rows and a formula references both of those rows, copying the formula to a cell which has two or more working rows will result in a relative formula being pasted into the target cells. The target cells need to have the same working row offset.

  • When you copy a formula that references a cell in one level, the formula you paste into the target cell, in a different entity in the same level, is relative. For example, if you copy a formula in Branch A’s Cost of Goods Sold, which references sales in Branch A, and paste it into Branch B’s Cost of Goods Sold, the formula will reference to Branch B’s sales.

Examples

The following examples might be useful in your budget or forecast worksheet.

Reference a cell within the same worksheet

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

Reference a cell in another worksheet

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.

Reference a cell in a comparison row

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.