It would be great if 1-2-3 had a goal-seeking function built into it. As this article shows, HP CALC can be used to perform such goal-seeking. It makes 1-2-3 appear to be solving the problem backwards. Backsolving, as it's called, gives added power to the "what-if" capabilities of 1-2-3.
We discussed the power of "backsolving" in the last issue of The HP Palmtop Paper (page 52, Nov/Dec 92). Solver's CALCCELL function lets you run the Lotus spreadsheet in reverse to do "goalseeking." The Lotus spreadsheet used in the last issue (INSURE.WK1 (ON DISK ICON)) compared the value of whole life vs. term insurance. The example given showed you how to use backsolving to find the annual premium for term insurance that gave a savings/loss figure exactly equal to zero. It turns out there's an easier way to get 1-2-3 to backsolve; a way that doesn't involve writing Solver programs. The recipe for doing this is well documented in the Users Manual. In my edition of the manual, the recipe starts on page 28-20. The subtitle is "Using the Solver in a 1-2-3 Worksheet." Please read or reread that section of the manual for a thorough explanation of the procedure. I won't repeat what the manual says. Rather, I'll show you a few more examples and point out a couple of the "gotchas" that I've discovered.
A Simple Example of Backsolving
One of the problems that occurs in many algebra texts goes something like this. "Given that Y = 4.32^X, find the value of X which will make Y equal to 234." That is to say, if 234 equals 4.32, raised to some unknown power, X, what's the value of X? To find this value by hand, you'd need to know something about logarithms. However, with your HP 95LX in hand, you can skip the logs and go directly to a solution in one of several ways. You can solve the equation by using Solver alone. On your 95LX, simply press (HPCALC) (MENU) Solve; then move to an empty line and press (F2) (Edit). Key in the equation Y=Z^X. Press the (F9) [Calc] key and key in 234 and press (F2). Key in 4.32, press (F3), and then solve for X by pressing (F4). The answer is 3.7282. (This assumes that Solve is set up to display four decimal points -- press (MENU) Options Format Fix 4.)
Now, let's see how to do the same thing using Lotus 1-2-3. Press (123) (the blue key) to call up an empty worksheet. If Lotus is already up with another worksheet, save the worksheet (press (MENU) File Save <filename> (ENTER)) and then erase it ((MENU) Worksheet Erase Yes).
Put the cursor in cell B2 and key in the formula +A1^A2 and press (ENTER). This is just another way of writing Z^X, except that the variables "A1" and "A2" reference spreadsheet cells. Cell B2 contains the formula and is used as a "Formula cell" in this example. (Don't worry about the "ERR" that appears in B2.) Put the cursor in cell A1. Key in the number 4.32 and press (ENTER).
Now press (HPCALC) to return to the "Solve Calc" screen. (If you've closed down HP Calc prior to this, you'll have to press (HPCALC) Menu Solve, move the highlight bar to Y=Z^X and press (F9) (Calc)). Press (ESC) (F8) to go to the "Solve 1-2-3" screen and again press (F8) (the Form key) to bring up the worksheet. Position the cursor in cell B2 and press (ENTER). Key in the value 234 and press (F9) (VALUE). Finally, press (F10) (Solve). This will return you to the worksheet. Move the cursor to the cell where the answer will appear (say, A2) and press (ENTER). The Solve 1-2-3 screen will reappear, the numbers will roll, and soon you'll see "Solve Result = 3.7282." Press the [1-2-3] blue key and observe that cell A2 now shows "3.728208."
Now, suppose you want to restore the worksheet to its original values. Press (HPCALC) to return to the "Solve 1-2-3" screen and press (F5) (UNDO). You'll get an error message that says "Error: 1-2-3 did not accept data." This happens because cell A2 was blank before we started backsolving. UNDO restores the original value, and for some reason Solver can't restore a cell which has no value (i.e. and empty cell). The work-around for this "gotcha" is to put zero, or any value, in cell A2 prior to backsolving. In other words, give Solver a value that it can restore and it will work like a charm.
A More Complicated Example From Financial Math
Suppose you're contemplating a mortgage for $125,000.00. The bank wants to charge 8% interest for a 30 year loan. You want to know what the monthly payments will be. Moreover, you want to know the total principal and interest paid back over 30 years.
You decide to use 1-2-3 to solve the problem rather than the TVM function in CALC. Press (123) and put the cursor in cell A5. Key in mortgage price, 125000, and press (ENTER). Next go to cell A6 to enter the interest rate. Key in 8/100/12 and press (ENTER). You should see "0.006666," the monthly interest rate. Next, move the cursor in cell A7 and key in 30*12 and press (ENTER) to get "360." Go to cell A8, key in @PMT(A5,A6,A7), and press (ENTER). The answer to the monthly payments will be "917.2057."
To find the total principal and interest, go to cell B8 and key in 360*A8. The cell displays "330194.0." Now go to cell B6, key in 12*100*A6, and press (ENTER) to see "8," the annual interest rate. Just out of curiosity, what kind of interest rate would bring the total principal and interest down to a nice, round number, say, 300,000.00? To find the answer, press (HPCALC) (F8) (1-2-3) (F8) (Form). When the worksheet appears, move the cursor to cell B8 and press (ENTER). This will be the formula cell for this example. Back in the "Solve 1-2-3 screen," key in 300000, press (F9) (VALUE) and (F10) (Solve). In 1-2-3 move the cursor to cell A6 and press (ENTER). The Solve 1-2-3 screen will appear and you'll see a message "Error: Solve cell must be a value." That's right, Solve can only change numbers. It can't modify formulas. So, press the 1-2-3 blue key. Press (F2) to bring the formula into the edit line. Press (F9) to force a recalculation and press (ENTER) to put the value in A6.
Return to Solver and press (F10) and (ENTER). The Solve result will be "0.0059." Push (123) and see that the monthly interest rate is 0.005850 and the annual interest rate is approximately 7.02%. We didn't backsolve for the annual interest rate (APR) because it doesn't work. If you try converting B6 (the APR cell) to a value and using it as the Solve cell, Solver will try to backsolve -- and fail. However, if you really want to backsolve on B6, here's how to modify the worksheet to make this happen.
In 1-2-3, put the value 8 in cell B6. In cell A6, key in the formula +B6/12/100 and press (ENTER). Repeat the backsolving operation using B6 as the Solve cell. This time the Solve result will be "7.0203," the APR.
What The Manual Doesn't Tell You
Solver can only backsolve on one value at a time. It can only find a single goal. However, you may want to see the effect that backsolving would have on many values at the same time. Here's an example of how this might work. Suppose you're running a small company that has four sales regions. The sales regions report the following year-end profits:
North-East: $100,200.00
South-West: $150,000.00
North-West: $50,000.00
South-East: $78,000.00
To find the total profit for the company, use the same 1-2-3 worksheet as before and enter the data in cells A10 through A13. Put the formula @SUM(A10..A13) in cell A14. You should see "378200" appear.
Assuming that the regions will perform proportionately as well next year, what profits should each region show to make the total profit equal $500,000.00? The solution to this puzzle lies in the word "proportionately." To see what this means, perform the following operations in 1-2-3.
Begin by copying cell A10 to B10. (Put the cursor on A10 and press (MENU) Copy (ENTER); move the cursor to B10 and press (ENTER).) Also copy the formula in cell A14, to cell B14. Cell B14 will act as the Formula cell for Solver. Cell B10 will act as the Solve Cell. Remember, it has to be a value and not a formula.
The rest of the data items must be formulas if this example is to work as advertised. Key into B11 the formula +A11*$B$10/$A$10. This gives us a number in B11 that is based on the value in B10. I've rearranged the terms slightly. The actual computations might look like 102000*(150000 /102000). The "$" signs force the cell references to be absolute rather than relative. When you key in the cell references, just push (F4) and the "$" signs will appear automatically.
Now copy this formula from cell B11 to cells B12..B13. (Put the cursor on B11 and press (MENU) Copy (ENTER); move the cursor to B12 and press (ENTER). Follow the same procedure for B13.) At this point the numbers in column B should look exactly like the numbers in column A.
When you're ready, press (HPCALC) and (F8) in the "Solve 1-2-3" screen. When the worksheet appears, make cell B14 the Formula cell by highlighting it and pressing (ENTER). Back in Solver, key in 500000 and press (F9) (VALUE) and (F10) (Solve). In the worksheet, highlight cell B10 and press (ENTER). Solver will backsolve and come up with "132,469.5928." Switch to 1-2-3 and see that all the other numbers in column B have changed as well:
B10 132469.50
B11 198307.70
B12 66102.59
B13 103120.00
B14 500000.00
If you get the drift of how this works, you should be able to extend the capabilities of Solver further.
Remember, to make Solve 1-2-3 work, you need one Formula cell and one Solve cell. The Solve cell must be a value, not a formula. The other data items can be formulas which somehow relate a data item to the Solve cell. In this example, the other three data items are proportional to the value of the Solve cell. However, you don't need to use a simple proportional relationship. You could make the formulas in cells B11 through B13 as complicated as you like. Go for it!
Use With Care!
Here's an oddity -- an example that should fail, but doesn't.
In the 1-2-3 worksheet, go to cell B16 and key in the formula @SQRT(A16) (square root of cell A16). Put the cursor in cell A16 and enter the value -10. "ERR" appears in cell B16 because Lotus 1-2-3 can't find the square root of a negative number.
But suppose you forgot that 1-2-3 had a @SQRT() function and tried to use backsolving to find the square root of a negative number.
In the 1-2-3 worksheet, go to cell B18 and key in the formula +A18^2 and press (ENTER). Press (HPCALC) and in the "Solve-1-2-3 screen" press (F8) to make B18 the Formula cell. Type in -10 and press (F9) to set the Formula value equal to -10. Press (F10) to Solve and make cell A18 the Solve cell. Watch the numbers roll. On my 95LX, the Solve result= -0.000000003. Is this a bug, or has HP CALC just backsolved itself into a corner? Who knows! I wouldn't trust the answer.