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)
| 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 |
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.
On this page
| Table of Contents | ||||
|---|---|---|---|---|
|