By Ed Keefe
In 1983, when HP teamed up with Lotus to create the HP Portable (110), they produced a laptop computer that was both rugged and useful. Without a doubt, the built-in Lotus 1-2-3 program was the eye-popping feature of the machine. With the spreadsheet program on board, users could take their applications into the field and update them in their car or from the back of their pickup truck.
When HP and Lotus teamed up to develop the HP 95LX Palmtop, they made sure Lotus 1-2-3 was built in. This time they created a computer that would let users keep spreadsheets in their pocket and update them almost anywhere.
With both the Portable and the first Palmtop, HP included a relatively complete set of instructions for the spreadsheet program. The HP 95LX also contained several exemplary spreadsheets in ROM. Without the printed instructions and the sample spreadsheets many users would have put Lotus 1-2-3 in the category of "nice-to-have-but-otherwise-useless."
However, when HP brought the 100/200LX to market, they omitted the instructions on how to use 1-2-3 and they removed the sample spreadsheets from ROM. They must have assumed that users knew how to use the then-standard spreadsheet program. If not, you could have bought a book on 1-2-3. The bookstore shelves were full of them. If you wanted sample spreadsheets you could have downloaded hundreds of them from CompuServe or from local bulletin boards.
What HP didn't take into account was that the DOS version of Lotus 1-2-3 was on the way out while the Windows version of Excel was on the way in. Books for MS-DOS versions of 1-2-3 vanished almost overnight and electronic bulletin board owners moved their DOS programs to offline archives. New owners of the HP Palmtop were left with legacy code and little help in using it.
Over the past eight years The HP Palmtop Paper has printed several articles and dozens of tips on how to use 1-2-3. Thaddeus Computing also sells the Microref "Quick Reference Guide to Lotus 1-2-3" both as a printed booklet and in electronic form on the 1999 and 2000 CD InfoBase. However, users keep asking for more. In particular, new owners of HP Palmtops want more and better examples of spreadsheet applications and better instructions on how to create their own spreadsheets using the 1-2-3 macro language. Without the additional resources, the built-in Lotus 1-2-3 is like a jig-saw puzzle for which most of the pieces are missing.
To track down some of the missing pieces for the puzzle, we searched our own archives, looked far and wide on the Internet and electronic bulletin boards for any and all references to MS DOS versions of 1-2-3. From the dozens of files we found, we selected what we considered to be the best and have included them on the 2000 CD InfoBase.
The files therein contain information both for beginning users of 1-2-3 as well as a wealth of material for advanced users. Here is a summary of what we found.
123BOOK.EXE (123BOOK.TXT) is a 24-page document in both text and electronic form that gives all the basic information about 1-2-3 (release 2.4) in the quickest way possible. If you're new to 1-2-3, here's the place to start. Once you've digested this material, you'll be ready for something like the Microref book.
If you're looking for something to take the place of the missing tutorial that comes with the full version of Lotus 1-2-3, take a look at the TechnoJock tutorials by Bob Ainsbury. There are three tutorials that deal with Printing, Databases, and Menus. All of these run on the Palmtop's version of 1-2-3.
If you want to read more tips about using 1-2-3, there is a wealth of material in the Lotus Tips collected by Dean Wood over a seven year period. The tips cover such topics as using date and time functions, using 1-2-3 as a database engine, sorting techniques, creating graphs, examining the structure of worksheet data files, using 1-2-3 as a word processor or outliner, and on and on. Most of the information is contained in a series of text files, but there are also a number of .WKS files. (The .WKS extension means that the sample worksheets should also work in early releases of the 1-2-3 as well as the version in the Palmtop.)
For those who want ready-to-use spreadsheets or who want an application that they can adapt to their own situation, there are several files that contain fully functional spreadsheets for scientific, statistical and business applications in 1-2-3.
For example, in the business area there are spreadsheets for tracking response for various advertising sources, for calculating depreciation using the MACRS method, for tracking mileage, car expenses, maintenance, etc., for determining what your billing rate should be, scheduling employees, estimating the cost for a number of jobs, tracking expenses on a monthly or quarterly basis, keeping track of frequent flyer miles, taking a complete inventory for a business, computing the payroll for up to 15 employees, keeping track of software registration codes, recording the scores for employees in a training program and tracking utility costs.
For those who are involved with statistics and want something more than the List/Stat program in HP Calc but less than a dedicated statistics program, the Blossom series of Lotus templates will let you perform the following statistical tasks: 1-way Analysis of Variance, Chi-squared test for an N x M contingency table, 2-way frequency table for two columns of data values, create graphs with confidence limits, produce single and double life tables with a significance test, perform linear regression with optional weighting, do parabolic regression, calculate statistical probability distributions and interpret all types of t-tests.
In the science and engineering area we found a 1-2-3 template that uses Lotus macros to emulate a scientific calculator -- one that you can modify. The calculator-emulator does...
1. Simple arithmetic.
2. Functions editing and function evaluations. The program can handle functions having from one to seven variables and beyond.
3. Physical units definition and conversion.
4. Integration and differentiation of analytic functions and data tables. You can key in the functions to integrate or differentiate or import them from a text file. The program will produce graphs of the functions before and after integration and differentiation.
5. Editing and calculating statistic functions on lists of numbers. Both the formulas and the data tables may be keyed in or imported as text files.
6. Frequency distribution and normal distribution of data.
7. Curve fitting (4 types).
8. Root finding (non-linear equations).
9. Matrix and vector operations.
10. Simultaneous linear equation solving.
(Note that HP Calc does many of the same calculations. However, you could use this spreadsheet application as an extension to HP Calc in the Palmtop.)
Lotus 1-2-3 without macros is merely another way to store information and perform a few mathematical calculations. With macros, you can make a spreadsheet program into a personal business or scientific tool. Yet, for many users, macros remain a mystery.
Admittedly, creating complicated macros is not something to be undertaken as a casual hobby. However there are add-ins and libraries of macros that can make the task a lot simpler. We've included a couple of macro libraries to let you explore this dimension of spreadsheet computing.
Forrest Harlow, creator of the BlackBox set of macros, has done the nitty-gritty work of testing and debugging the most commonly used macro commands and bundled them in what he calls "Black Boxes." Instead of writing your own low-level macro code you can build a spreadsheet program by snapping together pre-written modules. The modular approach is much faster to learn, easier to use, and yields more reliable programs. Of course, if you use the black box approach, you'll miss learning about all the low level macro commands: you'll also miss the headaches involved in getting them to work correctly.
On the other hand, if you really want to learn how to create your own "black boxes" in the Lotus 1-2-3 macro language, we've included two of the best references for this task.
R2MACS.WK1, by Craig Thurber, is an indexed reference guide to the use of the Lotus 1-2-3 advanced macro commands. It contains explanations and working examples of the commands. The reference guide is a spreadsheet in itself. When you load it into 1-2-3, you'll see a screen containing several subjects. You can highlight a subject of interest with the cursor and press Alt-G to go to that subject. There are many example macros included. To activate one of these examples, you simply point to it and press Alt+A.
The worksheet is its own manual. You can print it out by selecting Print under the General subject category on the first screen and running the macro named Print. The manual is 21 pages.
The most thorough reference for macros is from K.I.T.A.L. Software. It's a shareware product consisting of two parts. The first part is a macro commander and the second is an electronic book on 1-2-3 macro programming for intermediate to advanced users.
Macro Commander is a Lotus 1-2-3 add-in that speeds the entry of advanced macro commands into your spreadsheet. With Macro Commander, you can reduce spelling and syntax errors, quickly check a command's arguments and decrease your spreadsheet template development time. The operation is very similar to the way you might build a function in MS Excel on a desktop computer: namely, click on the name of a function and fill in the blanks.
The second part of the package consists of "Super Power Macros: Techniques and Secrets for Lotus 1-2-3." It is a hypertext electronic book about the Lotus macro language presented as a Microsoft Windows 3.1 help file. The .HLP file contains the equivalent of a 1,000 page printed book. The Help file will work in Windows 9x/NT as well as Windows 3.1. However, to make it useful on the Palmtop, we converted the .HLP file to an equivalent HTML package. You may need an extra memory card to accommodate all the files in the set. There is over 6MB of detailed information in more than 100 files.
"Super Power" presents a step-by-step analysis of over 200 macros. You can look up a macro, key it into a spreadsheet and run it. The text will explain what each command in the macro is supposed to do. Once you see how the macro works, you can either use it as-is or modify it to suit your own application. The macros contained in the book are masterpieces of the macro art. They include tricks and techniques that have not been printed in other books on the subject. The macros deal with the common tasks of printing, creating graphs, copying, moving, formatting output, etc. The unique feature about the macros in the "Super Power" book is that they work with ranges rather than columns or rows. As a result, they don't suffer from the limitations of other macro libraries. They don't demand that the data in a range has to be of the same type. For example a macro designed to change labels to uppercase will ignore all the other types of data and will treat only the labels in the range. If the macro is designed to multiply all the numbers by a factor, it will multiply all the numbers in the range and ignore all the other cells. The macros handle both 2-D and 3-D ranges transparently and automatically sense what version of Lotus 1-2-3 you're using and apply the correct code.
The collection of Lotus 1-2-3 files on the 2000 CD InfoBase provides a wealth of resources for both beginning and advanced users of the Palmtop's most powerful program. Hopefully it will answer the oft-asked question: "Where can I get more information about 1-2-3?" If you can't find it here, you probably won't find it elsewhere.