• Practice
  • About 14 min
  • 5 practice questions

Formulas and basic functions

Write and evaluate formulas using SUM, AVERAGE, MAX, MIN and COUNT and the order of operations.

If something looks wrong, please tell us.

Every formula in a spreadsheet starts with the equals sign (=). Without it, the cell holds plain text. Exams ask what a formula returns, so you must evaluate it by hand.

Common functions

Functions
FunctionReturns
=SUM(A1:A5)Total of the numbers in the range
=AVERAGE(A1:A5)Mean of the numbers
=MAX(A1:A5)Largest value
=MIN(A1:A5)Smallest value
=COUNT(A1:A5)How many cells hold numbers
=IF(A1>50,"Pass","Fail")Pass when A1 is above 50, otherwise Fail

Order of operations

Brackets first, then exponents, then multiplication and division (left to right), then addition and subtraction. So =2+3*4 gives 14, but =(2+3)*4 gives 20.

Worked examples

Example 1. Cells A1 to A4 hold 10, 20, 30 and 40. What do =SUM(A1:A4) and =AVERAGE(A1:A4) give? SUM = 100. AVERAGE = 100 / 4 = 25.

Example 2. B1 holds 8 and B2 holds 4. What does =B1+B2*2 give? Multiplication first: 4 x 2 = 8, then 8 + 8 = 16.

A formula starts with =. Multiply and divide before add and subtract, unless brackets say otherwise.

— Rule to remember

Key takeaways: start with =, follow the order of operations and learn the five basic functions.

Check your understanding

  1. 1. Every formula in a spreadsheet must begin with

    1. =
    2. #
    3. @
    4. $
    Show answer

    Answer: A. A formula starts with the equals sign; without it the entry is not calculated.

  2. 2. What does =5+10/5*2 return?

    1. 6
    2. 3
    3. 15
    4. 9
    Show answer

    Answer: D. Division and multiplication first, left to right: 10 / 5 = 2, then 2 x 2 = 4. Then 5 + 4 = 9.

  3. 3. B1 holds 6 and B2 holds 3. What does =B1+B2*4 return?

    1. 36
    2. 13
    3. 18
    4. 24
    Show answer

    Answer: C. Multiplication first: 3 x 4 = 12, then 6 + 12 = 18.

  4. 4. Three cells hold 12, 18 and 30. What is the AVERAGE of them?

    1. 60
    2. 20
    3. 18
    4. 30
    Show answer

    Answer: B. (12 + 18 + 30) / 3 = 60 / 3 = 20.

  5. 5. A1 holds 50. What does =IF(A1>50,"Pass","Fail") show?

    1. Pass
    2. 50
    3. Fail
    4. TRUE
    Show answer

    Answer: C. The test is 'greater than 50'. 50 is not greater than 50, so the test is false and IF returns Fail.

Privacy · Terms · Refunds · Delivery