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
23
Working with mathematical formulas
Textbook: pp. 103–105
GoalCalculates totals in several ways, copies formulas, fills with a step and solves tasks such as equations and date differences in a sheet.
New words
AutoSum (Σ): a button that inserts the SUM function for the neighbouring numbers · avtoyig‘indifill series: filling cells with numbers that change by a fixed step · qadam bilan to‘ldirishcompletion percentage: the amount done as a percentage of the plan · bajarilish foizidate difference: subtracting one date from another gives the number of days between them · sana farqi
Explanation
A total can be found in three ways: adding as in =C2+D2+E2+F2, the function =SUM(C2:F2), or the Σ (AutoSum, Alt+=) button; then we copy the formula down and the relative reference adjusts for each row. Completion percentage = amount done · 100 : plan; we put an absolute reference on the plan cell: =F2*100/$F$1. To fill a number sequence with a step, select two cells and drag the fill handle, or in Home → Fill → Series give the step and stop value. For the linear equation ax + b = 0 with a ≠ 0, x = –b/a; in a sheet: =IF(A1=0; IF(B1=0; "any x"; "no solution"); -B1/A1). Excel stores a date as a number, so the difference of two dates (later − earlier) gives the number of days; the result cell must be set to the “General” or “Number” format. To make a table of function values, fill the x column with a step, write the y formula in the first row and copy it down.
Worked examples
Completion percentage: the plan is F1 = 200 notebooks. A shop sold F2 = 150: =F2*100/$F$1 → 75 per cent. Copied to F3 it becomes =F3*100/$F$1 – the denominator stays fixed.
Table: y = 3x – 2 with x from –4 to 6 in steps of 2: x = –4, –2, 0, 2, 4, 6; y = –14, –8, –2, 4, 10, 16. Dates: 01.03.2024 − 01.01.2024 = 60 days (2024 is a leap year, February has 29 days).
Class activity
“Spreadsheet workshop”: a group builds a table of 3 months’ sales for 5 shops (made-up numbers) and calculates total, average and percentage of the plan for each; it checks how the formulas copy.
Practice
1
Find the sum 5 + 10 + 15 + ... + 100 (step 5). How would you calculate it in a sheet?
20 terms; the sum is 20 · (5 + 100) : 2 = 1050. In a sheet fill 5 to 100 with step 5 and use SUM.
2
In the percentage formula =F2*100/$F$1 why is $ used?
So the plan cell stays F1 when the formula is copied down.
3
In the table y = 3x – 2, what is y when x = 6?
16
4
How many days are there between 15.02.2024 and 01.03.2024 (2024 is a leap year)?