Review: working with mathematical formulas
In this practical lesson we meet new functions. COUNTIF(C2:C9; "<0") gives the number of negative values; COUNTIF(A1:A5; ">=60") counts scores of 60 or more. FIND("@"; A1) gives the position of the “@” sign in the e-mail address in A1, and an error (#VALUE!) if it is not found, so to check whether it is there we write =IF(ISNUMBER(FIND("@"; A1)); "yes"; "no"). REPT("*"; 5) gives five stars. ROMAN(2024) converts an Arabic number to Roman numerals (MMXXIV), and FACT(5) = 5! = 120. If an amount B is put into a bank deposit and M per cent is added each year (interest is added to the total each year), after A years the amount is =B*(1+M/100)^A. For example, 1,000,000 so‘m at 10 per cent for 3 years becomes 1,000,000 · 1.1³ = 1,331,000 so‘m. In calculations such as income tax, put the rate in its own cell so that if the rate changes the result updates.
The class monitor counted by hand those on the attendance list who came on at least 4 days of the week. Because the condition was exact, nobody was counted wrongly.
Accountants, survey takers and teachers count the records that meet a condition.
Write ten numbers in your notebook and count how many of them are greater than 5.
| A | B | |
|---|---|---|
| 1 | 45 | 3 |
| 2 | 60 | |
| 3 | 72 | |
| 4 | 58 | |
| 5 | 90 |
“Formula hunters”: the teacher states a result (for example “Roman numerals: MCMXC”) and groups find which function and argument give it.