Complex Formulas and Functions

Lessons

Senior 2 · ICT

Complex Formulas and Functions

5 lessons

Lesson 3 of 5

Mathematical Functions

20 min

Let's begin

Typing =B2+B3+B4+…+B46 to add 45 marks is slow and easy to get wrong. A function does the same job in a few characters: =SUM(B2:B46). Spreadsheets have hundreds of built-in functions. This lesson covers the six mathematical ones you need at this level, plus MIN.

What is a function?

A function is a predefined formula with a name. It is written as =NAME(arguments). The arguments are the values or cells the function works on, written inside the brackets and separated by commas. Depending on its language settings, Calc may show a semicolon (;) between arguments instead: =POWER(3;4) means the same as =POWER(3,4).

To see every function with a short explanation, open the Function Wizard: Insert > Function or Ctrl+F2 in Calc (Excel: Formulas > Insert Function, or Shift+F3).

The functions, using the marks table

	A	B	C	D
1	Name	Maths	English	ICT
2	Uwase	78	64	90
3	Mugisha	55	72	48
4	Keza	91	85	88
5	Ishimwe	43	50	61
6	Gatete	67	49	75

Function	What it does	Example	Result
SUM	Adds all the numbers	=SUM(B2:D2)	78 + 64 + 90 = 232
AVERAGE	Finds the mean: the sum divided by how many numbers there are	=AVERAGE(B2:B6)	334 ÷ 5 = 66.8
MAX	Finds the largest number	=MAX(B2:B6)	91
MIN	Finds the smallest number	=MIN(B2:B6)	43
SQRT	Finds the square root	=SQRT(144)	12
POWER	Raises a number to a power: POWER(number, power)	=POWER(3,4)	3 × 3 × 3 × 3 = 81
PRODUCT	Multiplies all the numbers	=PRODUCT(2,3,4)	2 × 3 × 4 = 24

Worked example: marks and a plot of land

In E2 type =SUM(B2:D2) and in F2 type =AVERAGE(B2:D2). Uwase: total 232, average 77.33 (rounded to 2 decimal places). Copy both down to row 6: Keza's total is 264 and her average 88.
Below the Maths column, =MAX(B2:B6) gives the top Maths mark, 91, and =MIN(B2:B6) the lowest, 43.
A square plot of land in Bugesera has an area of 144 m². Its side is =SQRT(144) = 12 m.
Four exercise books at 250 Frw cost =PRODUCT(4,250) = 1,000 Frw.

Two traps

Brackets. =B2+C2+D2/3 for Uwase gives 78 + 64 + 30 = 172, because only D2 is divided by 3. Use =AVERAGE(B2:D2), or =(B2+C2+D2)/3, which both give 77.33.
Empty cells. SUM and AVERAGE ignore empty cells and text. If Ishimwe's ICT mark were missing, =AVERAGE(B5:D5) would give the mean of 43 and 50, which is 46.5, dividing by 2, not 3. Type 0 if the student really scored zero.

Tip

Name, bracket, range, bracket. =SUM(B2:D2). Excel's AutoSum button (Home tab) and Calc's Select Function button (Σ) on the Formula bar insert SUM for you.

✦

Practice

Use the marks table.

1

What do =MAX(C2:C6) and =MIN(C2:C6) give?

Show solutionHide solution

Solution: English marks: 64, 72, 85, 50, 49. MAX = 85 (Keza), MIN = 49 (Gatete).

2

What does =SUM(B4:D4) give?

Show solutionHide solution

Solution: Keza: 91 + 85 + 88 = 264.

3

Work out =SQRT(81)+POWER(2,5).

Show solutionHide solution

Solution: SQRT(81) = 9 and POWER(2,5) = 2 × 2 × 2 × 2 × 2 = 32. Total: 9 + 32 = 41.

4

What does =POWER(5,2)*2 give?

Show solutionHide solution

Solution: POWER(5,2) = 25, then 25 × 2 = 50.

5

Which formula finds the average ICT mark?

Show solutionHide solution

Solution: =AVERAGE(D2:D6). (90 + 48 + 88 + 61 + 75 = 362, and 362 ÷ 5 = 72.4.)

Remember

A function is written =NAME(arguments).
SUM adds, AVERAGE finds the mean, MAX and MIN find the largest and smallest, SQRT finds the square root, POWER(n, p) raises n to the power p, PRODUCT multiplies.
SUM and AVERAGE skip empty cells and text.

Sources and credits

Getting Started Guide 26.2, Chapter 3: Getting Started with Calc, LibreOffice Documentation Team, The Document Foundation, licensed under CC BY 4.0. Adapted by Ganzaa.
The explanations, worked examples and practice items were written by Ganzaa.

1What is a function?

2The functions, using the marks table

Worked example: marks and a plot of land

3Two traps

Practice

What is a function?

The functions, using the marks table

Two traps