• Foundation
  • About 17 min
  • 5 practice questions

Functions: SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, IF, ROUND and text functions

Use the everyday functions to total, average and count a list, make a decision with IF, round results, work with text, and recognise the common error values.

If something looks wrong, please tell us.

Adding fifty cells with + signs is slow and easy to get wrong. A function is a ready-made formula with a name: you give it the cells to work on and it returns the answer. A handful of functions covers most office work, and they have the same names in Calc and Excel.

How a function is written

A function is written as =NAME(arguments), for example =SUM(B2:B6). The arguments go inside the brackets; several arguments are separated by a comma in Excel. Calc traditionally uses a semicolon, =SUM(B2:B6;D2), and its separator can be set in its options; these lessons write commas. The AutoSum button (Σ) inserts a SUM of the cells above or to the left in one click.

The everyday functions on one marksheet

Marks in B2:B6 - one entry is text
ItemAB
1NameMarks
2Asha72
3Bilal45
4Charu88
5DevAbsent
6Esha60
Text such as "Absent" is skipped by SUM, AVERAGE, MIN, MAX and COUNT
FormulaWhat it doesResult
=SUM(B2:B6)Adds the numbers265
=AVERAGE(B2:B6)Sum of numbers ÷ how many numbers66.25
=MIN(B2:B6)Smallest number45
=MAX(B2:B6)Largest number88
=COUNT(B2:B6)Counts cells holding numbers4
=COUNTA(B2:B6)Counts cells that are not empty5

Worked example 1: why the average is 66.25

SUM adds the four numbers: 72 + 45 + 88 + 60 = 265. AVERAGE divides by the count of numbers, which is 4, not 5: 265 ÷ 4 = 66.25. The text "Absent" is ignored, and so would an empty cell be. But a cell holding 0 is a number and is counted. COUNT gives 4 (numbers only) while COUNTA gives 5 (anything that is not empty, including text).

COUNT or COUNTA?

  • COUNT
    • Counts cells holding numbers
    • Skips the text Absent
    • Result on the marksheet: 4
  • COUNTA
    • Counts cells that are not empty
    • Includes text
    • Result on the marksheet: 5

IF: making a decision

=IF(condition, value if true, value if false). For example, =IF(B2>=50,"Pass","Fail") shows Pass for Asha (72 is at least 50) and Fail for Bilal (45 is not). Text answers go inside double quotes; numbers and formulas do not need them, as in =IF(B2>80,B2*10%,0).

ROUND: changing the value, not just the look

=ROUND(number, digits) rounds to the given number of decimal places, with 5 and above rounding up. =ROUND(66.25,1) gives 66.3, =ROUND(3.14159,2) gives 3.14, and =ROUND(47.368,2) gives 47.37. A negative digit count rounds to tens, hundreds and so on. Unlike a number format, ROUND changes the value that later formulas use.

Number format or ROUND?

  • Number format
    • Changes how the value looks
    • Calculations use the stored value
  • ROUND
    • Changes the value itself
    • Later formulas use the rounded value
    • =ROUND(66.25,1) gives 66.3

Worked example 2: text functions

  • =LEN("New Delhi") → 9 (the space counts as a character).
  • =LEFT("New Delhi",3) → New; =RIGHT("New Delhi",5) → Delhi.
  • =MID("COMPUTER",4,3) → PUT (start at the 4th character, take 3).
  • =UPPER("ccc") → CCC; =LOWER("CCC") → ccc; =PROPER("ravi kumar") → Ravi Kumar.
  • =TRIM(" ravi kumar ") → ravi kumar (extra spaces removed).
  • =CONCATENATE(A2," ",B2) or =A2&" "&B2 joins two cells with a space between.

Common error values

What the messages mean (Calc may also show codes such as Err:502 for some other problems)
ShowsUsual causeExample
#DIV/0!Dividing by zero or by an empty cell=A1/B1 when B1 is empty
#NAME?A name the program does not know: misspelt function, or text without quotes=SUMM(A1:A3); =IF(A1>40,Pass,Fail)
#VALUE!The wrong kind of data, such as text in arithmetic="ten"+5
#REF!A referenced cell no longer exists, usually because it was deletedDeleting column B used by =B2*2
#N/AA lookup did not find the valuea lookup for a missing roll number
#####Not an error: the column is too narrowa long number in a narrow column

SUM, AVERAGE, MIN, MAX and COUNT use numbers only; COUNTA counts every non-empty cell. IF(test, if true, if false) - text in quotes.

Key takeaways: use functions instead of long chains of +; remember that text and blank cells are skipped by AVERAGE and COUNT; put text results of IF in quotes; ROUND changes the value; and read an error value as a clue to the cause.

Check your understanding

  1. 1. A1:A4 hold 10, 20, the text abc, and 30. What does =COUNT(A1:A4) return?

    1. 3
    2. 4
    3. 60
    4. 1
    Show answer

    Answer: A. COUNT counts only cells holding numbers: 10, 20 and 30, which is 3. COUNTA would give 4 because it also counts the text. 60 is the SUM.

  2. 2. What does =AVERAGE(12,18,30) return?

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

    Answer: B. Sum = 12 + 18 + 30 = 60, and there are 3 numbers, so the average is 60 ÷ 3 = 20.

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

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

    Answer: A. The test is 33 >= 33, which is true because 'greater than or equal to' includes equal. So the value-if-true, Pass, is shown.

  4. 4. What does =ROUND(47.368,2) return?

    1. 47.36
    2. 47.4
    3. 47.37
    4. 47
    Show answer

    Answer: C. Two decimal places: look at the third decimal, 8, which is 5 or more, so the second decimal rounds up from 6 to 7, giving 47.37.

  5. 5. A cell holds =SUMM(A1:A3). What does it show?

    1. #VALUE!
    2. #REF!
    3. #DIV/0!
    4. #NAME?
    Show answer

    Answer: D. SUMM is not a function the program knows, so it reports #NAME?. #VALUE! is for the wrong type of data, #REF! for a reference that no longer exists, and #DIV/0! for division by zero.

Privacy · Terms · Refunds · Delivery