Lessons 17 · 1 lessons · N. I. Taylaqov (general editor), A. B. Akhmedov, M. D. Pardayeva, A. A. Abdug‘aniyev, U. M. Mirsanov. Informatics and Information Technologies, Grade 10, 1st edition. “Extremum-Press”, Tashkent, 2017
17
Solving some problems with MS Excel
Textbook: pp. 39–41
GoalFinds the root of an equation with a table of values and a sign change, solves a system by the intersection of graphs, solves a quadratic with formulas and calculates with dates.
New words
graphical method: the solution is read from where graphs meet or cross the x axis · grafik usulsign change: f(a) and f(b) have opposite signs, so a root lies between a and b · ishora almashishidiscriminant D = b² – 4ac: its sign tells how many real roots a quadratic has · diskriminantGoal Seek: an Excel tool that changes one cell until a formula gets the target value · Goal Seek
Explanation
Excel also helps to solve equations approximately. For an equation f(x) = 0 a table of x values and the formula for f(x) are built: if f(a) and f(b) have opposite signs, there is a root in the interval [a; b]. Then the step in that interval is made smaller and the root is found to the accuracy needed; Data – What-If Analysis – Goal Seek does the same job. To solve a system of two equations, both are brought to the form y = … and their graphs are drawn in one chart: the intersection point is the solution. A quadratic equation ax² + bx + c = 0 can be solved with formulas: calculate D = b² – 4ac and, if D ≥ 0, x = (–b ± √D)/(2a). In Excel dates are stored as serial numbers, so the difference of two dates gives the number of days between them: =DATE(2026,4,1)-DATE(2026,3,1) = 31; today’s date is taken with TODAY().
ƒFormula
D = b² – 4ac; x = (–b ± √D) / (2a)
roots exist if D ≥ 0
💡 Where will I use this?
In real life
In a “guess my number” game your friend answers only “higher” or “lower”. If you always name the middle of the range, you find the number fast, because the range halves each time. Finding the root of an equation in a table works the same way: the interval where the sign changes keeps narrowing.
Who uses it
Engineers, economists and scientists solve equations that have no simple formula approximately, using tables and graphs.
Try it today
Let a friend think of a number from 1 to 50, try to find it using only “higher/lower” answers and count your guesses; starting from the middle works best.
Worked examples
x³ + x – 3 = 0. f(1) = –1, f(2) = 7: the sign changes, so the root is in (1; 2). With step 0.1, f(1.2) = –0.072 and f(1.3) = 0.497: the root is in (1.2; 1.3). With step 0.01, f(1.21) = –0.0184 and f(1.22) = 0.0358: the root is between 1.21 and 1.22, about 1.213.
The system x + y = 5, x – y = 1. The first is y = 5 – x, the second y = x – 1. The two lines meet at (3; 2): 3 + 2 = 5 and 3 – 2 = 1. The quadratic x² – 5x + 6 = 0: D = 25 – 24 = 1; x = (5 ± 1)/2, that is 3 and 2.
Class activity
“Besiege the root”: pairs narrow down the root of x³ + x – 3 = 0 first with step 1, then 0.1 and 0.01; the pair that first finds the root to 0.001 wins.
Practice
1
At which point do the lines y = 2x + 1 and y = –x + 4 meet?
(1; 3)
2
For f(x) = x² – 2x – 3, f(2) = –3 and f(4) = 5. What follows?
The sign changes, so there is a root in (2; 4) (in fact x = 3).
3
What is D for x² – 5x + 6 = 0?
1
4
Why does applying SQRT give an error when D < 0?
A negative number has no real square root, so Excel gives #NUM!; that is why IF(D>=0, …) is used as protection.