Loading

Everything HP200LX: Knowledge, Products, Service 

HOW TO USE: Getting Lotus 123 and HP CALC to Work As a Team!Ed demonstrates how the 95LX's CALCCELL function integrates Lotus 123 and HP CALCBy Ed KeefeIntegrating Lotus 123 and HP CALCLet me describe a feature of the solve program that will make your life in the math lane much simpler. This technique ties together Lotus 123 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 123 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 123 with a blank worksheet. Set column A to a width of 5 ((MENU) Worksheet Column Setwidth 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 A11: 'in Premiums A12: 'Savings (+) Loss () A13: 'with Term Ins D11: @FV((D3D2)/12,(D9/12)/100,D5*12) D13: +D11D7 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 123 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. 123 can't solve such a "backsolving" and uses the HP CALC program. Keep 123 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 123 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 2924 to 2927.) 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 123 found in other spreadsheets, the ability to do "goalseeking". It does this in a way that is far better than any other goalseeking 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 123. 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 123 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! 

Copyright © 2010 Thaddeus Computing Inc