ACCA Applied Knowledge · Management Accounting

Spreadsheets: formula sheet

Full chapter guide

Key formulas

Cell reference: Column letter + row number, e.g. C4; Letter first, then number. C4 is column C, row 4.
Range: First cell:Last cell, e.g. B2:B6; The colon means 'from B2 to B6'. This range has 5 cells.
Formula entry: = followed by the calculation, e.g. =B2*C2; Without the = sign, the spreadsheet treats the entry as text.
Sum of a range: =SUM(B2:B6); Adds every number in the range.
Absolute reference: $B$2; The $ signs fix the cell when a formula is copied. B2 without $ will shift.
Basic formula: =B2*C2; Always starts with =. Use cell references, not typed numbers, so results update.
SUM: =SUM(B2:B10); Adds every number in the range B2 to B10. The colon means 'from ... to'.
AVERAGE: =AVERAGE(B2:B10); Mean of the numbers in the range. Empty cells are ignored, but zeros are counted.
IF: =IF(condition, value_if_true, value_if_false); Example: =IF(B2>100, "Over", "Within"). Text results need quotation marks.
VLOOKUP: =VLOOKUP(lookup_value, table, column_number, FALSE); Looks in the first column of the table. FALSE gives an exact match. Column number counts from the first column of the table as 1.
HLOOKUP: =HLOOKUP(lookup_value, table, row_number, FALSE); Looks across the top row of the table. Row number counts from the top row as 1.
ROUND: =ROUND(number, number_of_digits); 2 gives two decimal places, 0 gives a whole number, -3 rounds to the nearest thousand.
Absolute reference: $A$1; The $ locks the column and the row. Press F4 in Excel to add the dollar signs.
Chart choice: trend over time: Time series data → line chart; Time goes on the horizontal axis. Use for sales by month or cost trends.
Chart choice: comparison: Categories compared → bar or column chart; Best for comparing departments, products or periods side by side.
Chart choice: proportion: Parts of one whole → pie chart; Use only with a small number of categories. Parts must add to 100%.
Chart choice: relationship: Two variables → scatter chart; Used to spot correlation, for example advertising spend against sales.
Pivot table layout: Rows + Columns + Values (+ Filters); Values are summarised by sum, count or average. Refresh after the source data changes.
Percentage of total: Part ÷ Total × 100; Used for pie charts and for pivot tables showing percentage of grand total.
Model structure: Inputs → Calculations → Outputs; Keep the three areas separate and labelled. Change assumptions only in the inputs area.
Prevent, detect, correct: Preventive control (stops error) | Detective control (finds error) | Corrective control (fixes error); Validation and protection are preventive. Reviews, cross-checks and error-checking tools are detective. Backups help recovery.
Absolute reference: $B$2 (fixed) | B2 (relative) | $B2 or B$2 (mixed); Use $ to stop a reference moving when a formula is copied. A missing $ is a classic error.
Common Excel error values: #DIV/0! = divide by zero | #REF! = invalid cell reference | #NAME? = unrecognised name or function | #VALUE! = wrong data type | #N/A = value not found; These are visible errors. Wrong but plausible results show no message.
Check total: Total by rows − Total by columns = 0; A cross-check that should equal zero. Any other result signals an error.

Quick revision

A workbook holds worksheets. A worksheet is a grid of rows, columns and cells.
A formula starts with = and can use cell references, numbers and operators.
Relative references change when copied. Absolute references, such as $A$1, stay fixed.
Brackets control the order of calculation. Multiplication and division come before addition and subtraction.
SUM adds a range, AVERAGE gives the mean, and MAX and MIN give the largest and smallest values.
IF tests a condition and returns one result if true and another if false.
Lookup functions find a value in a table based on a matching key.
Use line charts for trends over time, bar charts to compare categories and pie charts for parts of a whole.
Pivot tables summarise large data sets without changing the original data.
Data validation limits what can be entered into a cell.
Keep inputs, calculations and outputs separate and label them clearly.
Test a model with simple figures whose answer you already know.

Common mistakes

Confusing rows and columns, for example saying column 3 or row C. Fix: Remember: letter first, number second. Columns go up and down like pillars.
Confusing a worksheet with a workbook. Fix: A workbook is the file. A worksheet is one tab inside it.
Forgetting the $ signs when copying a formula that uses a fixed rate Fix: Ask which input must not move. Lock it with $, for example $B$1.
Counting the VLOOKUP column number from column A of the sheet Fix: Count from the first column of the table range as 1.
Using a pie chart for time series data or many categories Fix: Use a pie chart only for parts of one whole with few categories. Use a line chart for time and a bar chart for many categories.
Thinking filtering deletes rows Fix: Remember that a filter only hides rows. The data stays in the file and returns when the filter is cleared.
Saying data validation stops all errors. Fix: Validation only restricts what can be entered. A valid number can still be the wrong number, and it does not check formulae.
Confusing cell protection with data validation. Fix: Protection stops changes to locked cells such as formulae. Validation controls the type or range of values typed into input cells.

Exam tips

Know the layout terms exactly: cell, row, column, range, worksheet, workbook. These are easy marks if you are precise.
For 'select two' questions, pick exactly two. Choosing more or fewer scores nothing.
When asked for a disadvantage, link it to accounting: an unnoticed formula error in a budget can lead to wrong decisions.
Watch for options that say spreadsheets are 'always accurate' or 'never need checking'. They are almost certainly wrong.
In number entry questions on ranges, include both end cells when counting.
Read the formula as if you are the computer. Substitute the values and work it out. Do not guess from how it looks.
For copy-and-paste questions, write the new formula for the target cell before looking at the options.
Check the last argument of VLOOKUP and the column number first. These are the usual traps.