Excel and Lotus
software are equipped with functions that allow the user to identify the root
of an equation. By root, we mean the values of x such that a
given equation cancels itself out.
Let us consider the case where we wish to
obtain the root of the function
2
|
3
|
4,
|
|||||
i.e.,
solve the equation
|
. You will see in the following illustration,
|
||||||
that the
first step of all
|
Excel solutions is to correctly define the function we want to find
|
||||||
2
|
3
|
4
|
0
|
||||
the root of, and to assign the variable
to one specific cell.
We assigned the
cell B1 to contain the value of variable x. In cell B2, we define
the function. Note that B1 plays the part of in the formula. By
inserting values in cell B1, you will notice that the result of the function
will change. Therefore, finding the root of a function implies finding the
value of such that the function is zero. Our goal is to have cell B1 vary (the
value of x) until the cell B2 (that contains the value of the
function) is 0.
It would be too
long to find an answer by trial and error. Select in Excel the function Solver
(Tools menu). The following dialog box will appear.
Set Target Cell: we ask you to identify the position of the function you wish to carry
out an operation on. In our example, the function was placed in cell B2.
Equal to: To identify the operation you wish to carry out with the function
located at B2 (max ? min ? value ?). We want the function to take on the
value 0.
By Changing Cells: To identify cells that contain the variables of the function. In our
example, B1 is the cell containing the value of x.
By clicking
on Solve, Excel will execute the operation you asked it to and will give
you the solution x = 0,85078105.
One of the
limits of the Excel Solver is that it will only give one root even though we
know there is a second one. For now, we use the discriminant
√
|
4
|
|||
That gives the second root (
|
2
|
).
|
||
2,35078108
|
||||
Unfortunately, the
discriminant will be of no use with other types of functions and we will not be
able to count on it to help us.
Exponential and logarithmic equations
using Excel
Besides for finding
the root of polynomial equations, the Excel Solver can solve equations
containing exponential or logarithmic functions. The software will be all the
more useful in this case since solving this type of algebraic equations is
often impossible. For example, there are no solution methods that will find the
value of such that the equation 4 is solved.
The method to follow will be identical to the one presented in the case of
roots, except for the following detail : the equation must be rewritten so that
all terms are regrouped on the left side of the equality. For example, instead
of resolving the equation 4 , we want the
solution of 4 0.
Example
Solve the following equation with the
help of the solver:
|
4 .
|
Solution
Firstly, we need to rewrite the equality
so that the terms are regrouped on the left side :
4
0. Then, it
is important to correctly define the equation we want to resolve, as well as attributing the variable to a specific cell.
Once again, we have
designated the cell B1 to contain the value of the variable . The cell B2
contains the expression equal to 0. It is defined in function to B1, which
substitutes the variable .
Select in Excel the
function Solver (Tools menu). A dialog box will appear in which you need to
fill in information.
Equal to: To identify the operation you wish to
carry out with (max ? min ? value ?).
Variable cells: To identify cells
that contain the variables of the function. In our example, B1 is the
cell containing the value of x.
Target cell
to define: the function was placed in cell B2.
Equal to: We want the function located in B2 to take on the value 0.
Variable cells: B1 is the cell that will contain the value of .
By clicking
on Solve, Excel will execute the operation you asked it to and will give
you the following answer:
The value of
|
makes the equations
|
true. Let
us recall though
|
||||
that
Excel only
|
gives one solution to any given equation, even though it may have
more
|
|||||
0,35740306
|
4
|
|||||
than one.
A second solution
|
exists when
|
, as is indicated in the
|
||||
following image:
|
2,15329217
|
|||||
The techniques
allowing us to find these other solutions will be the subject of future
discussions.
With the
help of Excel, find at least one value of x such that the
following equations are satisfied.
a)
|
ln
|
2
|
|||
b)
|
|||||
c)
|
ln 1
|
||||
2
|
|||||
d)
|
√
|
||||
ln
|
3
|
||||
Solution
a. 1,68957994
b. 0,76822112
c. 0,44754188
d. 0,82461277
