Some Thoughts on Benchmarking Portable Spreadsheets

Testing spreadsheets is a problem even in the world of "big computing." The array of functions and capabilities are bewildering. How can you tell if a unique function in one spreadsheet gives any real advantage over a completely different unique function in another spreadsheet? In the field of portable spreadsheets the problem is compounded by the overall integration with other functions outside the spreadsheet, such as efficiency of file loading and memory manipulation, and the effect of unique hardware designs. So far I have not had the opportunity to address these questions. To do so would require costly, time consuming surveys of actual usage by large numbers of people.

The only relevant measure that I could test with any degree of reliability was speed of calculation. But in practice, the only "fast" thing you can do with a pocket spreadsheet is display an existing sheet with existing data. In that case, the main concern are the difficulty of access, and the amount and clarity of the information displayed.

Setting up or entering data on most pocket spreadsheets takes so much time due to interfacing limitations such as the size of the keys or speed of writing numbers on a screen, that large spreadsheets are avoided. It might be that calculation speed can fairly easily reach a level of being "reasonable", and any advantage of additional speed beyond that point could be negligible. But right now, nobody knows. The only thing we know for certain is that faster spreadsheets are never a bad thing.

The spreadsheet test I've been using is a variation of the original Byte Magazine Spreadsheet Benchmark, which even Byte doesn't refer to anymore. The benchmark is a pure floating point multiplication test and it is generally accepted that this is not representative of common spreadsheet usage. But since no one has suggested a better test for pocket spreadsheets, it is valuable to at least have a test that can be compared to previous desktop technology.

The Benchmark as I'm currently using it is defined as follows: Set a value of "0" in cell A1. Cell A2 (or B1) is the value of A1 times 1.001. Each cell following that is equal to the preceding cell times 1.001. For the actual benchmark, the number "1" is entered into the A1 cell and the relevant time includes calculation and redisplay if possible, including the last value. My current standard is to calculate 400 cells.

Even after deciding the mathematically content of the test, the layout is still an issue. The original layout for the Byte benchmark was row oriented based on "B1=A1*1.001". But desktop computers could be presumed to have fairly large displays, even in the early days. Many early business computers used terminals with 80 column by 24 line displays, and 80 column cards were quickly available for Apple II computers. Since displays in pocket devices might always be limited to relatively low resolution displays, if for no other reason than human eyesight, I felt that a specific principle recognizing this limitation should be adopted. It seems likely that most people will design their spreadsheets in the ways that are conceptually simple, easily entered, and use the displays to their fullest. Therefore, it is proper to adapt the spreadsheet layouts to conform to these goals.

The layout that I used reported in my test of the Psion Series 3a was 4 columns of 100 cells based on "A2=A1*1.001" ending with E1=D100*1.001. This layout was easily created with block copy commands, and because the numbers 1 - 100 are known, I didn't have to calculate the numerical equivalent values for columns beyond "z". A horizontal layout would give slightly more efficient screen usage, but the easiest horizontal layout was 8 lines of 50, ending with "A9=AX8*1.001". Even then, row 9 is not completely displayed on the default Psion display. I could have worked out a horizontal layout, but in real life, I simply would not bother, so that was my reason not to do so in this test.

Another aspect of visual layout was number format. In most cases, I currently test using fixed 2 decimal place formats. The difference can be substantial and I believe that the vast majority of spreadsheet usage is monetary calculation. I could specifically use the "monetary" formats that are common in modern spreadsheets, but since they need not be used for monetary spreadsheets, I don't have any reason to believe that people generally would use them if a speed difference was evident and so I don't use them in the test.

Working with the Psion reveals another choice. When a sheet is loaded the first "recalc" takes longer than subsequent recalculations. Both numbers are relevenant and given enough space in a magazine article, I would have reported them both, but the space was not available. So which number to chose? I took the figure for subsequent recalculations because it was the most likely to be consistent. Also, since I tried to test file load time separately anyway, that much of the test would be redundant and any variability due to that component was unnecessary corruption of the test.

Times with number format fixed 2 decimal places:
Full time to load and "recalc" the vertical version of the spreadsheet:
About 38 seconds.
Enter "0" recalc time about 2.6 sec.
Enter "1" recalc time about 3.5 sec.
This "3.5" value is the one that I have generally use for comparisons.

Other Possible Results that were Not Reported:
For comparison, here are figures for recalc for "default" Psion numberical formats. Most numbers have 6 decimal places:
Enter "0" recalc time about 2.8 sec.
Enter "1" recalc time about 3.6 sec.

Horizontal layout results were different:
Load and recalc, horizontal sheet 2, fixed 2 decimal places:
about 58 - 59 sec.
Enter "0" recalc time about 5.1 sec.
Enter "1" recalc time about 6.0 sec.

Recalc, horizontal sheet, default format (6 decimal places):
Enter "1" recalc time about 6.1 sec.

Since the default Psion display shows 6 columns (with the last partially hidden) and 9 rows (with the last partially hidden) it would be seem the most of the difference in time is taken by formatting and displaying the numbers on the screen despite the fact that most of the numerical calculation time was for "off screen" values. The last run of the spreadsheet with "default" number formatting taking only slightly longer indicates that it doesn't help much to reduce it to 2 decimal places. A corollary is that most spreadsheets, including some that are probably much more complex than this will take under 6 seconds to run, since that is about the longest that any would take to display.

Another aspect of the benchmark that went unreported due to space was the final answers. When fully displayed the "vertical" version and horizontal versions of the sheet both yield the same answer: 1.49152656125747. Other spreadsheets yielded slightly different answers. Unfortunately, I did not have the time for a proper test of precision.
[1997/01/02]