Complex Formulas and Functions
Senior 2 · ICT
Complex Formulas and Functions
Lesson 4 of 5
20 min
Let's begin
At the end of the year the school needs a decision for each student: Pass or Fail in each subject, and Promote or Repeat overall. With 45 students and 12 subjects, deciding each one by hand takes hours and invites mistakes. Logical functions make the decision automatically from a rule, and conditional formatting colours the results so problems stand out.
A condition (or logical test) is a comparison that is either TRUE or FALSE, for example B2>=50. These two values are called logical values.
| Function | Result |
|---|---|
| IF(test, value if true, value if false) | Gives one result if the test is TRUE and another if it is FALSE. |
| AND(test1, test2, …) | TRUE only if all the tests are TRUE. |
| OR(test1, test2, …) | TRUE if at least one test is TRUE. |
| NOT(test) | Reverses the result: TRUE becomes FALSE and FALSE becomes TRUE. |
Text results, such as "Pass", must be in double quotation marks. Numbers and cell references are not.
| 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 |
AND for Gatete is FALSE because one test (English 49 ≥ 50) fails; OR is TRUE because one test (Maths 67 ≥ 50) passes. The grade formula is a nested IF: an IF inside another IF. The spreadsheet checks the tests in order and stops at the first TRUE one: 78 is not ≥ 80, but it is ≥ 60, so the result is B. In the last line, IF uses AND as its test: a student is promoted only if all three marks are 50 or more. Gatete repeats because of English 49.
Conditional formatting changes how a cell looks (its colour, font or border) automatically, depending on its value. If the value changes, the format changes too.
Three cells turn red: Mugisha's ICT (48), Ishimwe's Maths (43) and Gatete's English (49). Ishimwe's English mark of 50 is not less than 50, so it stays plain. The same menu offers colour scales, data bars and icon sets, which shade every cell according to its size.
Tip
AND is strict, OR is generous. AND needs every test to pass; OR needs just one. For IF, read it aloud: "IF this is true, then show this, otherwise show that."
Use the marks table.
What does =IF(C3>=50,"Pass","Fail") give?
Solution: C3 is Mugisha's English, 72. 72 ≥ 50 is TRUE, so: Pass.
What does =AND(B3>=50,D3>=50) give?
Solution: Mugisha: 55 ≥ 50 is TRUE but 48 ≥ 50 is FALSE. AND needs both, so: FALSE.
What does =OR(B5>=60,D5>=60) give?
Solution: Ishimwe: 43 ≥ 60 is FALSE but 61 ≥ 60 is TRUE. OR needs just one, so: TRUE.
What grade does =IF(B6>=80,"A",IF(B6>=60,"B","C")) give Gatete?
Solution: 67 is not ≥ 80, but it is ≥ 60, so: B.
Write a formula for E2 that shows "Merit" if Uwase's average is 60 or more, and "-" otherwise.
Solution: =IF(AVERAGE(B2:D2)>=60,"Merit","-"). Her average is 77.33, so it shows Merit.
Remember
Sources and credits
=IF(B2>=50,"Pass","Fail") Uwase, Maths 78 -> Pass
=IF(B5>=50,"Pass","Fail") Ishimwe, Maths 43 -> Fail
=AND(B6>=50,C6>=50) Gatete: 67 and 49 -> FALSE
=OR(B6>=50,C6>=50) Gatete: 67 or 49 -> TRUE
=NOT(B5>=50) Ishimwe: NOT(43>=50) -> TRUE
=IF(B2>=80,"A",IF(B2>=60,"B","C")) Uwase 78 -> B, Keza 91 -> A, Mugisha 55 -> C
=IF(AND(B2>=50,C2>=50,D2>=50),"Promote","Repeat") Uwase -> Promote, Gatete -> Repeat