Readers of the last issue know that I am experiencing some problems with my eyesight. My vision has improved since I wrote the last column, but is still severely impaired. On a recent visit to an optometrist, I got some pointed advice about my visual problem, which leads to some interesting insights about using the 95LX.
Adapt to Your Circumstances
The conversation with the optometrist began like this:
Dr: "From how far away do you watch television?"
Me: "It's across the room."
Dr: "Could you see it better if you were closer?"
Me: "Yes?"
Dr: "Why don't you get closer?"
It's pretty obvious advice for someone with poor eyesight, and the general principle, adapting to the circumstances of your situation, is pretty good advice for HP 95LX users. A Palmtop is not as powerful as a desktop computer. Smaller and more limited versions of programs, including 1-2-3 spreadsheets, may be more appropriate for use on the 95LX. Even alternate forms of analysis may be appropriate.
Don't Base Your Actions on Incorrect Assumptions
The conversation continued:
Dr: "Bright light bother you?"
Me: "No."
Dr: "Then why do you own a pair of prescription sunglasses?"
I assumed that I needed sunglasses -- I didn't. When writing computer programs, or designing Lotus worksheets, a common and potentially erroneous assumption is that a certain value is constant. One of "Murphy's Laws" for computer programmers is: "Constants aren't". As soon as you put a constant into a program (or spreadsheet) you discover that the value needs to be able to change and you really should have put in a variable. A good programmer, spreadsheet designer, or analyst of any kind will check the validity of his or her assumptions by varying all the elements of the equation. It helps to show the effects of changing the underlying assumptions, and can help to define the critical variables in a problem.
Likewise, it is a fact that computer analysis may not produce a definitive result. Assumptions may be wrong, and different forms of analyzing a problem may produce very different results.
Test the Results to See if They Are Reasonable
The optometrist tested my eyes with a standard eye chart:
Dr: "Read line three from the left, there are no numbers on the chart."
Me: "M, L, R, 8, ... "
Dr: "I said there were no numbers!"
This is a straightforward example of a simple principle: Test your results to see if they are reasonable.
I once worked with a person who so distrusted machine-generated documents that he was accused of proofreading Xerox copies. This person, a computer programmer, knew that there is almost a 100% probability of errors creeping into complex computer routines. He also knew that the best way to catch errors is to verify the results of complex analyses and even simple calculations.
Verifying results is applicable on a much wider scope than computer analysis. For example, an accountant would never think of doing a spreadsheet without "cross footing" it to verify accuracy. Cross footing is the process of comparing the sum of row totals and column totals -- they should be equal.
The accountant's advice can be incorporated into a computer program or spreadsheet. It's tough to stop someone (including yourself) from inputting a bad value into a spreadsheet. But it is easy to write a formula to display "ERR" if an unacceptable value is input into a cell. Consider the following formula evaluating input in a cell range named "OBJECT" to determine if the input is in the range of acceptable values defined by cells "HIGH" and "LOW".
@IF(OBJECT>=LOW#and#OBJECT<
=HIGH,0,@ERR)
A range of acceptable values may be "static" (hard coded into the spreadsheet), or "dynamic" (computed within the spreadsheet). The accountant's cross foot is a dynamic range in that the sum of rows must equal the sum of columns, which can change.
This results check can be fouled by one of the perversities of computers unless it is designed with a small amount of tolerance. Computers "think" using binary numbers, but display results and receive input in decimal numbers (the preferred human number system). A decimal fraction often cannot be converted to binary number without some slight error. The result is that two numbers that should be equal may vary slightly because the underlying numbers have slightly different conversion errors. Testing for equality results in identifying an error when, in fact, one does not exist. The formula below provides a bit of tolerance and uses the @ABS function to allow for plus and minus 0.001 variation.
@IF(@ABS(@SUM(ROW)-@SUM(COL))
<0.001,0,@ERR)
The cross foot is an example of verification where you test for equality in two different calculations. However, sometimes you can take a shortcut and test for approximate equality. For example, you know the sum of a row needs to be within a certain range, or the average of the numbers in a row will be within specified fixed variance. Application of shortcut calculations requires knowledge of the system being analyzed and some foresight.
One might wonder why a well-tested spreadsheet should contain a lot of error checks. The answer is simple: How else do you get a well checked spreadsheet? Writing error tests during development is the best way to assure good logic. In addition, error tests protect you from accidentally pressing the wrong key and turning a formula into a constant (i.e. 1-2-3 "sees" text entered into a cell as a label and evaluates it as zero).