Complex Formulas and Functions
Senior 2 · ICT
Complex Formulas and Functions
Lesson 5 of 5
18 min
Let's begin
The bursar's fee register was typed by three different people. Some names are in capitals and some are not, phone numbers start with 0 when the SMS system needs +250, and some fee cells are blank. Before sending reminders, the bursar needs to count how many students have paid and tidy the text. Counting functions and text functions do this without retyping anything.
Everything here works the same way in LibreOffice Calc and Microsoft Excel unless a difference is pointed out. Suppose A2:A8 of the fee register holds: 15000, "paid later", (empty), 12000, "exempt", (empty), 15000.
| Function | Counts | =FUNCTION(A2:A8) |
|---|---|---|
| COUNTA | cells that are not empty: numbers, text, anything | 5 |
| COUNTBLANK | cells that are empty | 2 |
| COUNT | cells that contain numbers only | 3 |
Check: 5 non-empty + 2 empty = 7 cells, the size of A2:A8. COUNT gives 3 because only the three amounts are numbers.
A text string is a piece of text. Inside a formula it goes in double quotation marks. Positions in a string are counted from 1, starting at the left.
| Function | What it does |
|---|---|
| UPPER(text) | Changes all letters to capitals. |
| LOWER(text) | Changes all letters to small letters. |
| REPLACE(text, start, number of characters, new text) | Removes a number of characters from a starting position and puts new text in their place. |
| SEARCH(find, within text) | Gives the position where the text you are looking for first appears. It ignores capitals and small letters. If the text is not found, it gives an error. |
REPLACE("S2A-2025",5,4,"2026"): count the positions S(1) 2(2) A(3) -(4) 2(5). Starting at position 5, remove 4 characters ("2025") and put in "2026". REPLACE("0788123456",1,1,"+250") removes the first character, the 0, and puts +250 in its place: the international form of a Rwandan number. SEARCH("@",…): k(1) e(2) z(3) a(4) @(5), so the @ is at position 5; an email address with no @ would give an error, which helps find typing mistakes. SEARCH("GALI","Kigali") gives 3 even though the capitals differ, because SEARCH ignores case. (FIND does the same job but is case-sensitive.)
These functions usually work on cells rather than typed text. If A2 holds "kEZA uwase", then =UPPER(A2) shows KEZA UWASE. Copy the formula down the column to fix the whole list at once.
Tip
COUNTA counts "anything", COUNTBLANK counts "nothing", COUNT counts "numbers". For REPLACE, remember the order: text, where to start, how many to remove, what to put in.
A2:A9 holds 6 names and 2 empty cells. What do =COUNTA(A2:A9) and =COUNTBLANK(A2:A9) give?
Solution: COUNTA = 6 (the names) and COUNTBLANK = 2. Check: 6 + 2 = 8 cells in A2:A9.
What does =LOWER("MUSANZE") give?
Solution: musanze.
What does =SEARCH("u","Musanze") give?
Solution: M(1) u(2), so 2.
What does =REPLACE("Huye",1,4,"Butare") give?
Solution: Starting at position 1, remove 4 characters (the whole word) and put in "Butare": Butare.
A column of fee amounts contains 40 numbers, 3 cells with the text "exempt" and 2 empty cells. What does COUNT give, and what does COUNTA give?
Solution: COUNT counts numbers only: 40. COUNTA counts every non-empty cell: 40 + 3 = 43.
Remember
Sources and credits
=UPPER("kigali") -> KIGALI
=LOWER("NYABARONGO") -> nyabarongo
=REPLACE("S2A-2025",5,4,"2026") -> S2A-2026
=REPLACE("0788123456",1,1,"+250") -> +250788123456
=SEARCH("@","keza@school.rw") -> 5
=SEARCH("GALI","Kigali") -> 3