Loading input.gsc-input { border-color: #BCCDF0; } input.gsc-search-button { border-color: #666666; background-color: #CECECE; }
 Everything HP200LX: Knowledge, Products, Service
 Archives 9 years, 100's of pages of HP Palmtop Paper, jammed with tips, reviews and how-to's Links Most useful, up-to-date Web sites devoted to the HP 200LX Downloads Direct link into 1000's of pieces of Palmtop software Tips from Our Techs 2008 Catalog (PDF, 2.6 MB) Contact Us/About Us
 Weekly Palmtop Paper Newsletter (Palmtop tips, latest happenings, and HP Palmtop Paper Store information)
HOW TO USE: Advanced / Backsolving with Lotus and HP Calc

# HOW TO USE: Getting Lotus 1-2-3 and HP CALC to Work As a Team!

### By Ed Keefe

Integrating Lotus 1-2-3 and HP CALC

Let me describe a feature of the solve program that will make your life in the math lane much simpler. This technique ties together Lotus 1-2-3 and HP CALC's SOLVE function. Once you have seen this technique, you will be able to design interchangeable solution programs in SOLVE with one or two lines of code.

To show you how this technique works, I'll use INSURE.WK1 , a simple 1-2-3 worksheet that compares the value of whole life vs. term insurance.

If you don't get The HP Palmtop Paper ON DISK and want to follow along on your 95LX, start 1-2-3 with a blank worksheet. Set column A to a width of 5 ((MENU) Worksheet Column Set-width 5 (ENTER)) and set the width of column D to 12.

Now key in the following worksheet labels and functions. (If you want to save a lot of keystroking, just key in the formulas in cells D11 and D13. The labels in column A are just reminders, anyway.)

Cell Contents

A1: ' Whole Life vs. Term Insurance

A2: 'Ann'l Prem for Term

A3: 'Ann'l Prem for Life

A4: 'Whole Life Cash Val

A5: 'Period (years)

A6: 'Whole Life Cash Val

A7: 'at end of Per.

A8: 'Avg Ann'l Int Rate

A9: 'over Period

A10: 'Future Value of Diff

A12: 'Savings (+) Loss (-)

A13: 'with Term Ins

D11: @FV((D3-D2)/12,(D9/12)/100,D5*12)

D13: +D11-D7

When finished, your worksheet should look like this Graphic

Key in the numbers as shown below. If necessary, set the number of decimal places to 2 in cells D2, D3, D7, D11, and D13 (press (MENU) Range Format Currency 2 while in the cell. (This makes the numbers in those cells appear with currency formats.)

At Cell: Key in

D2: 310

D3: 453

D5: 25

D7: 5000

D9: 6

The last two lines will not appear on the HP 95LX. Press (<DownArrow>) to scroll the screen to see the "bottom line."

D11: \$8,258.18

D13: \$3,258.18

In other words, if you invested the money you saved with term insurance at a 6% annual rate, you would earn \$5,000 more over 25 years.

Backsolving Using HP CALC with Lotus 1-2-3

Now, suppose you wanted to run the program in reverse to find what annual premium for term insurance would give a saving/loss figure of \$0.00 exactly. 1-2-3 can't solve such a "backsolving" and uses the HP CALC program.

Keep 1-2-3 running and press (HPCALC). Then press (MENU) Solve to activate the SOLVER. Press (F2) to start editing and type in the following formula. (You can press (<Tab>) for new line within HP Solve editor.)

CALCCELL(

[TermPrem,d2],

[LifePrem,d3],

[LCVPeriod,d5],

[LCValue,d7],

[AnIntRate,d9],

d13)=

SavLoss

Press (F9) (Calc) when you've entered the above and key in the same set of numbers as you did before.

TermPrem = 310

LifePrem = 453

LCVPeriod = 25

LCValue = 5000

AnIntRate = 6

Press (F7) SavLoss should = 3,258.18 (assuming that you've used the command: (MENU) Options Format Fix 2 to set decimal places to two).

Now to find what annual term insurance premium would give a saving/loss figure of \$0.00. With the highlight bar on SavLoss, type in 0 and press (F7). Then press (F2) to determine the Term Premium. The answer is: TermPrem = 366.42.

Switch back to 1-2-3 and notice that \$366.42 is now in cell D2. You have "backsolved" for that value. You can delete everything in the D column except the formulas in D11 and D13 and the CALCCELL C formula will still work. Just go to CALC and press (F7) and all the numbers in the CALCCELL formula will be reestablished in the worksheet. (For more about this trick, see the HP 95LX User's Guide, pages 29-24 to 29-27.)

This technique will work with any spreadsheet that requires a number of input cells and produces a "bottom line" result. One stipulation mentioned in the HP 95LX Users Guide, is that the spreadsheet must be active, and must have "READY" showing in the upper right corner. Also, the spreadsheet's input cells must not be protected.

How CALCCELL Works

CALCCELL() provides one of the missing parts of Lotus 1-2-3 found in other spreadsheets, the ability to do "goal-seeking". It does this in a way that is far better than any other goal-seeking routine that I've seen. Not only can you solve for one variable in terms of another, you can solve for ANY variable in terms of all the others at the push of a function key.

CALCCELL is made up of a series of input "arguments". Each argument is enclosed in square brackets. Inside each pair of brackets are two parts; the menu prompt for SOLVE and the cell reference for 1-2-3. Each of these two parts is separated by a comma.

If there is more than one input argument, the pairs of square brackets are separated by commas as well.

The final argument, inside CALCCELL's parentheses, is the 1-2-3 output cell. It is the value in this cell that is made equal to the label on the right side of the equal sign in SOLVE.

Backsolving is one of those rare instances in which the whole is greater than the sum of its parts. All that is left to do is to add CALCCELL's power to your old spreadsheets.

Until next time, Happy Palming!