The trial and error approach consists of guessing the answer and using
some sort of check to determine if that answer matches the supplied conditions
and any known relationships. While typically this is considered "cheating,"
the main objection is that unless it is done methodically, it is highly
inefficient. However, there is a whole branch of mathematics (numerical
analysis) devoted to doing just this. Several of the methods developed
in this field are actually incorporated into Excel in a very useful way.
Starting
with a simple example of how trial and error might work, we will try to
apply these techniques to a whole range of problems. Suppose you were
told that y=-3*x+5 and were asked to find the value of x such that y is
1, but knew nothing about the basic rules of algebraic manipulation (add
or subtract the same number from both sides, or multiply or divide both
sides by the same non-zero amount). Your only recourse would be to try
different values of x until you hit upon one that produces the right y
value. Now you could randomly try different numbers and perhaps never
find the right one (there being infinitely many numbers to choose from),
or you could use "educated guesswork". Suppose you try x=1.
The result is y=2, not too bad - means we probably aren't far off, but
which direction should we go? There are three possibilities. Our next
guess could be even farther off in the same direction, it could be closer,
or it could overshoot the answer. Depending upon which result we get we
will modify our approach. In the first case, we would probably want to
reverse directions. In the second case we would want to either make a
bigger or a smaller jump depending upon how close we are getting. And
in the last case we would want to shoot in between our last two guesses.
So lets try x=2. This gives y=-1. This qualifies as an overshoot, so I
know my answer is between 1 and 2. I can then keep refining my guess until
I get closer and closer to the real answer. This can be done manually
in Excel by making a column of x guesses and another column of calculations
for y (use the autofill operation to generate each new calculation). Since
the solution turned out to be a repeating decimal it took quite a few
calculations to peg it down to 6 places. The official name for this approach
is the "bisection method" of finding roots to equations.
While rather straightforward and quite effective for solving a great
many problems, doing this by hand is quite tedious, even with the help
of Excel's autofill. Fortunately, Excel provides us with a relatively
painless and automatic way of doing this (actually even a bit smarter
than the bisection method given above).
Goalseek
This rather powerful tool is, appropriately enough, found under the Tools
menu. It will guess at the value stored in one cell until the results
of a calculation in another cell reach a particular value. In this particular
instance it will guess at x in A2 until the y calculated in B2 becomes
equal to 1.
The first thing Goalseek asks you for is location of the calculation
whose desired result you already know. Then it asks you what that value
is supposed to be. Finally it asks you for the location of the value that
this calculation is dependent on (but the specific value necessary to
acheive the desired result is still unknown). It is absolutely essential
that the calculation in the first cell must, either directly or indirectly,
refer to the address of the value in the last cell. If the first cell
contains a value instead of a calculation it won't work. If the last cell
contains a calculation instead of a value it also won't work. Once you
click on OK, Goalseek will search for and display its solution.
It
is quite natural to use Goalseek as sort of an inverse function. A function
takes a known input value, such as the contents of A2, applies a rule
(the calculation in B2) and generates a single output value (the displayed
result in B2). An inverse function takes the known output and determines
what input created it. However, we can also apply Goalseek to more general
equations in the form "left expression" = "right expression"
as long as there is only one variable involved. For instance, to solve
2*x-6 = -x/2+1, we would set up 4 columns in Excel. The first column x
would contain a random value for x. The next two columns would have calculations
for the left and right sides of the equation respectively. The final column
would have a calculation of the difference between the right and left.
For x to be a solution, the left hand side would have to equal the right
hand side and the difference would be zero. So we use Goalseek to force
D2 to be 0 by changing A2. The end result is that A2 must be 2.8, which
is the solution.
When the expressions involved are more complicated (usually when they
are nonlinear) then there may be more than one possible solution. Goalseek
will only find one of the possibilities, depending on what x value you
start with. Occasionally there is NO solution, in which case Goalseek
will tell you that it can't find anything.
Solver
Excel has a somewhat more flexible tool that can be loaded as one of
the Add-ins, called "Solver". Although it can accomplis the
same task, as shown below, it has the added capability of being able to
determine when the calculation in a particular cell reaches a minimum
or a maximum value (if it does) and, even better, simultaneously juggle
multiple input values until it finds a combination that works. The problem
is that often there is more than one combination that appears to work
- typically it will find the one closest to the values you started with.
Some Sample Exercises
Use Goalseek to determine what value of x will make y = 1000e-0.12x
= 500. Bear in mind that you should put some random value of x
in one cell and a calculation for y in another cell. This particular
calculation in Excel will look like =1000*EXP(-0.12*?), where ? is the
address of the x value. Notice the EXP( ) function as a way to represent
e^(-0.12*x). You will conclude by using Goalseek to force the calculation
to a value of 500 by changing the value of the x.
Use Goalseek to find the intersection of two lines from the last set
of exercises, y = -x/2+3 and y = 2*x+1. Recall that the intersection
of the two lines is the value of x where the two y values are the same.
So you can treat one line as the left side of an equation, and the other
line as the right side, and determine the solution x when the difference
between the two sides is 0.
Use Solver to find at which value of x the parabola y = 2*x^2-8*x+5
reaches a minimum (vertex). Instead of checking the "Value of:"
radio button in the dialog above, check the "Min" one. Once
again you will need one cell for the random x value, and another for
the calculation of y based on it.