Skip to content

Management Accounting · Spreadsheets

Spreadsheet Formulae and Functions for ACCA MA

Updated 11 October 2026 · Fact-checked

A spreadsheet formula starts with = and calculates using values or cell references. Functions such as SUM, AVERAGE, IF, VLOOKUP and ROUND are built-in formulae. Use relative references (A1) when a formula should shift as it is copied, and absolute references ($A$1) when it must stay fixed. Check the result by reading the formula.

Understand Spreadsheet Formulae and Functions

A formula is an instruction you type into a cell. It always starts with an equals sign. For example, =B2*C2 multiplies the values in cells B2 and C2. If B2 changes, the answer updates by itself. That is why spreadsheets are useful for budgets and cost calculations.

A cell reference tells the formula where to find a value. A relative reference such as B2 changes when you copy the formula to another cell. Copy =B2*C2 down one row and it becomes =B3*C3. An absolute reference such as $B$2 does not change when copied. The dollar signs lock the column and the row. A mixed reference such as $B2 or B$2 locks only one part.

A function is a ready-made formula with a name and brackets. The values inside the brackets are called arguments. SUM adds a range. AVERAGE finds the mean. ROUND rounds a number to a set number of decimal places. These save time and reduce errors.

IF and lookup functions help you make decisions. IF tests a condition and gives one result if it is true and another if it is false. VLOOKUP searches down the first column of a table for a value, then returns a value from the same row in a column you choose. HLOOKUP does the same across the top row of a table and returns a value from a row below.

In the MA exam you are not expected to build a full model. You are usually shown a formula or a small sheet and asked what a formula will return, which formula is correct, or what happens when it is copied. So you need to read formulae accurately.

Key formulas to remember

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.

How to solve Spreadsheet Formulae and Functions questions

Use this method for any question on formulae, references or functions.

  1. 1Read the question and note what the formula must produce: a total, an average, a decision or a looked-up value.
  2. 2Identify which cells hold the inputs and which cell holds the result.
  3. 3Check the structure: starts with =, brackets match, and arguments are in the right order and separated by commas.
  4. 4For a copied formula, decide which references must stay fixed. Fixed inputs such as a tax rate or overhead rate need $ signs.
  5. 5For IF, work out whether the condition is true using the actual values, then pick the matching result.
  6. 6For VLOOKUP or HLOOKUP, find the lookup value in the first column or top row, then count to the column or row number given.
  7. 7For ROUND, look at the digit after the rounding position. 5 or more rounds up.
  8. 8Check your answer against the figures to see if it is sensible.

Quickest way: Substitute and count

When to use it: Use this when you must choose the correct formula or find the result of a function in a multiple choice question.

  1. Replace each cell reference with its value and calculate the result.
  2. For copy questions, write the new formula for the target cell by shifting only the relative parts.
  3. For lookups, count columns or rows from 1 starting at the table's first column or top row.
  4. Eliminate options with missing =, wrong brackets or text without quotation marks.

Common mistakes in Spreadsheet Formulae and Functions

  • Forgetting the $ signs when copying a formula that uses a fixed rate

    Relative references are the default, so the rate cell moves down as the formula is copied.

    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

    Students count the worksheet columns, not the columns in the selected table.

    Fix: Count from the first column of the table range as 1.

  • Using TRUE or leaving out the last argument in VLOOKUP when an exact match is needed

    The approximate match is the default and can return a wrong row without warning.

    Fix: Use FALSE for an exact match unless the question is about banded values.

  • Confusing VLOOKUP with HLOOKUP

    Both look similar and both return a value from a table.

    Fix: V is vertical: the lookup values run down the first column. H is horizontal: they run across the top row.

  • Mixing up ROUND with formatting

    Changing the number of decimals displayed looks the same as rounding.

    Fix: Remember that ROUND changes the stored value. Formatting changes only what you see.

  • Leaving out quotation marks around text in IF

    Students treat words like numbers or cell names.

    Fix: Put text results inside double quotation marks, such as "Yes".

Worked examples

Example 1

Cell B1 holds an overhead absorption rate of $4 per hour. Cells A3 to A5 hold hours of 10, 20 and 30. In B3 you enter =A3*B1 and copy it down to B5. What formula appears in B5, and what should it have been?

Show the solution
  1. Copying down moves each relative row reference down by two rows.
  2. A3 becomes A5 and B1 becomes B3, so B5 holds =A5*B3.
  3. B3 holds the formula =A3*B1, which returns 10 × $4 = 40. So B5 returns 30 × 40 = 1,200, not the right answer.
  4. The rate must be locked, so the original formula should be =A3*$B$1.

Answer: B5 contains =A5*B3, which returns 30 × 40 = 1,200, not the correct $120. It should have been =A3*$B$1 so that B5 gives 30 × $4 = $120.

Example 2

A price table has product codes in A2:A4 (P1, P2, P3) and unit prices in B2:B4 ($12, $15, $20). A sales sheet shows code P3 in D2 and 50 units in E2. What does =IF(E2>40, E2*VLOOKUP(D2,A2:B4,2,FALSE)*0.9, E2*VLOOKUP(D2,A2:B4,2,FALSE)) return?

Show the solution
  1. VLOOKUP finds P3 in column A of the table, which is in row 4.
  2. Column 2 of the table is the price column, so the price is $20.
  3. Test the condition: E2 is 50, and 50 > 40 is true.
  4. Use the true result: 50 × $20 × 0.9.
  5. 50 × $20 = $1,000 and $1,000 × 0.9 = $900.

Answer: $900

Exam tips

  • 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.
  • In multiple response questions, select exactly the number of answers asked for, and check each statement on its own.
  • In number entry questions, check whether the result needs rounding and follow the stated decimal places.

Practice questions from Spreadsheets

Spreadsheet Formulae and Functions: frequently asked questions

What is the difference between relative and absolute cell references?

A relative reference such as A1 changes when the formula is copied to another cell. An absolute reference such as $A$1 stays fixed. Use absolute references for constants like rates that every copy of the formula must use.

What is the difference between VLOOKUP and HLOOKUP?

VLOOKUP searches down the first column of a table and returns a value from another column in the same row. HLOOKUP searches across the top row and returns a value from another row in the same column. Use the one that matches how your table is laid out.

How do I use the IF function for accounting decisions?

Write a test, then what to return if it is true and if it is false. For example, =IF(B2>C2,"Over budget","Within budget") flags costs above budget. Text results go in quotation marks.

Which Excel functions matter most for the MA exam?

Know SUM, AVERAGE, IF, VLOOKUP, HLOOKUP and ROUND, plus relative and absolute references. Questions usually ask you to read a formula, pick the correct one or find its result rather than build a model.