☰ Contents · Computing

Solving equations in Excel

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().

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?
2
For f(x) = x² – 2x – 3, f(2) = –3 and f(4) = 5. What follows?
3
What is D for x² – 5x + 6 = 0?
4
Why does applying SQRT give an error when D < 0?