Solving Problems
Trial and Error
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.
 ...Previous
Page |
Contact Leo Wibberly at ldwibber@vcu.edu or (804) 740-4650 to make appointments