☰ Contents · Computing

Working with mathematical formulas

Lessons 23–24 · 2 lessons · B. Boltayev, A. Azamatov, A. Asqarov, M. Sodiqov, G. Azamatova. Fundamentals of Informatics and Computer Technology, Grade 8. Second edition. “O‘zbekiston milliy ensiklopediyasi” State Scientific Publishing House, Tashkent, 2015
24

Review: working with mathematical formulas

Textbook: pp. 106–107
GoalUses functions in harder tasks: counting, searching, repeating, factorial and compound growth.
New words
COUNTIF(range; condition): counts cells that meet a condition · COUNTIFFIND(what; where): the position of a text inside another text, or an error if it is not found · FINDREPT(text; times): repeats a text a given number of times · REPTcompound interest: interest added to the amount each year, so next year’s interest is on the larger sum · murakkab foiz
Explanation

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.

Worked examples
COUNTIF(A1:A5; ">=60") gives 3 if A1:A5 = 45, 60, 72, 58, 90 (the values 60, 72 and 90).
Compound interest: 2,000,000 so‘m, 10 per cent a year, 2 years: 2,000,000 · 1.1 · 1.1 = 2,420,000 so‘m.
Class activity

“Formula hunters”: the teacher states a result (for example “Roman numerals: MCMXC”) and groups find which function and argument give it.

Practice
1
If C1:C6 = –2, 5, 0, –7, 3, –1, what is COUNTIF(C1:C6; "<0")?
2
How much will 500,000 so‘m be after 2 years at 10 per cent a year (compound)?
3
What does =REPT("ab"; 3) give? What is its LEN?
4
Why is it convenient to store the interest rate in a separate cell rather than inside the formula?