• Foundation
  • About 13 min
  • 5 practice questions

Common functions: SUM, AVERAGE, MAX, MIN and COUNT

Use the standard functions on a range and work out what each one returns.

If something looks wrong, please tell us.

Adding 50 cells with plus signs would be tiring. Functions are ready-made formulas that do such jobs on a whole range.

Main functions

Functions
FunctionDoesExample
SUMAdds the numbers=SUM(B2:B6)
AVERAGEMean of the numbers=AVERAGE(B2:B6)
MAXLargest number=MAX(B2:B6)
MINSmallest number=MIN(B2:B6)
COUNTHow many cells hold numbers=COUNT(B2:B6)
IFChooses between two results=IF(B2>=40,"Pass","Fail")

The parts of a function

A function has a name, then brackets holding its arguments, here the range. The colon in B2:B6 means every cell from B2 to B6.

Worked examples

Suppose B2 to B6 hold the marks 40, 55, 60, 45 and 50.

Example 1. =SUM(B2:B6) = 40 + 55 + 60 + 45 + 50 = 250.

Example 2. =AVERAGE(B2:B6) = 250 / 5 = 50.

Example 3. =MAX(B2:B6) = 60 and =MIN(B2:B6) = 40.

Example 4. If B2 holds 40, what does =IF(B2>=40,"Pass","Fail") show? The test 40 >= 40 is true, so it shows Pass.

SUM adds, AVERAGE means, MAX largest, MIN smallest, COUNT counts numbers, IF decides.

— Remember

Key takeaways

  • Functions work on ranges: =NAME(range).
  • COUNT counts numbers only; IF returns one of two results.
  • Check your answers by hand on small ranges.

Check your understanding

  1. 1. B2 to B6 hold 30, 45, 60, 25 and 40. What does =SUM(B2:B6) return?

    1. 200
    2. 40
    3. 60
    4. 160
    Show answer

    Answer: A. 30 + 45 + 60 + 25 + 40 = 200.

  2. 2. B2 to B6 hold 30, 45, 60, 25 and 40. What does =AVERAGE(B2:B6) return?

    1. 45
    2. 50
    3. 40
    4. 200
    Show answer

    Answer: C. The sum is 200 and there are 5 numbers, so the mean is 200 / 5 = 40.

  3. 3. B2 to B6 hold 30, 45, 60, 25 and 40. What does =MIN(B2:B6) return?

    1. 30
    2. 60
    3. 25
    4. 40
    Show answer

    Answer: C. MIN gives the smallest number in the range, which is 25.

  4. 4. A1 to A4 hold 12, the word Ravi, 7 and 9. What does =COUNT(A1:A4) return?

    1. 4
    2. 28
    3. 3
    4. 1
    Show answer

    Answer: C. COUNT counts only cells that hold numbers: 12, 7 and 9. The word is not counted, so the answer is 3.

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

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

    Answer: A. The test 40 >= 40 is true (equal counts), so IF returns its first result, Pass.

Privacy · Terms · Refunds · Delivery