- 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
| Item | A | B |
|---|---|---|
| 1 | Name | Marks |
| 2 | Asha | 72 |
| 3 | Bilal | 45 |
| 4 | Charu | 88 |
| 5 | Dev | Absent |
| 6 | Esha | 60 |
| Formula | What it does | Result |
|---|---|---|
| =SUM(B2:B6) | Adds the numbers | 265 |
| =AVERAGE(B2:B6) | Sum of numbers ÷ how many numbers | 66.25 |
| =MIN(B2:B6) | Smallest number | 45 |
| =MAX(B2:B6) | Largest number | 88 |
| =COUNT(B2:B6) | Counts cells holding numbers | 4 |
| =COUNTA(B2:B6) | Counts cells that are not empty | 5 |
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
| Shows | Usual cause | Example |
|---|---|---|
| #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 deleted | Deleting column B used by =B2*2 |
| #N/A | A lookup did not find the value | a lookup for a missing roll number |
| ##### | Not an error: the column is too narrow | a 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. A1:A4 hold 10, 20, the text abc, and 30. What does =COUNT(A1:A4) return?
- 3
- 4
- 60
- 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. What does =AVERAGE(12,18,30) return?
- 60
- 20
- 15
- 30
Show answer
Answer: B. Sum = 12 + 18 + 30 = 60, and there are 3 numbers, so the average is 60 ÷ 3 = 20.
-
3. A1 holds 33. What does =IF(A1>=33,"Pass","Fail") show?
- Pass
- Fail
- TRUE
- 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. What does =ROUND(47.368,2) return?
- 47.36
- 47.4
- 47.37
- 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. A cell holds =SUMM(A1:A3). What does it show?
- #VALUE!
- #REF!
- #DIV/0!
- #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.
Sign in to keep track of the lessons you finish.