TellTable-S:
Server for Collaborative Office Applications

Spreadsheet tests

• 14 July 2004: Computation load spreadsheet tests
  Andy Adler

    Description:

  This test is described in the paper: TellTable Spreadsheet Audit: from technical possibility to operating prototype by John Nash, Andy Adler, Neil Smith (2004), EuSpRIG 2004, Conf. of the European Spreadsheet Risks Interest Group July 15 -16, 2004 Klagenfurt, Austria. The tests in this paper are based on a spreadsheet that requires significant computations, without the need for very large spreadsheet files which would, instead, be a test of the physical and virtual memory systems. Since we were unable to find such a test in the literature, a custom test spreadsheet was designed. We describe it here because it may constitute a useful benchmark. It is available as part of the telltable-s distribution (repository/files/timetest.sxc)

  The large computational load is due to multiple invocations of the matrix inverse. The test spreadsheets are built up with a border (left and top) that is a few (e.g., two) cells thick. Cell B2 can be provided with an arbitrary number to force recalculation. The rest of the “border” is built up of simple pattern data and trigonometric functions that ensure the matrix inverses described below can be computed. Each cell in the main block of the spreadsheet is the upper left or 1,1 element of the matrix inverse of the largest available square block of cells to the upper left of that cell. The test is then performed by adjusting the value of the arbitrary number in cell B2, and waiting for the spreadsheet to recalculate. The size of the spreadsheet is determined by the number of elements in column A and row 1 to the right and below cell C3. The spreadsheet is layout is shown in the following table,

  	A	B	C	D
  1		0	=B1+1	=B2+1
  2	0	Arbitrary number	=sin( B2+B1 )	=sin( C2+C1 )
  3	=A2+1	=cos( B2+A2 )	Matrix Inverse
  4	=A3+1	=cos( B3+A3 )

  where the Matrix Inverse element C3 is defined as:

      =INDEX(
          MINVERSE(
              OFFSET(
                  $A$1;
                  1 + MAX( 0; $A3 - C$1 );
                  1 + MAX( 0; C$1 - $A3 );
                  MIN( C$1;$A3 );
                  MIN( C$1;$A3 )
                  )
              );
          1; 1)
  

  This cell formula is then replicated for all cells below and right of C3. This spreadsheet has been tested on the spreadsheets OpenOffice.org calc, Microsoft Excel, and Gnumeric. Note that all semicolons (;) must be replaced by commas (,) for Excel and Gnumeric.

  This spreadsheet was tested on a Pentium 4, 2.4 GHz computer running Windows XP for spreadsheets of size 30x30, 35x35, 40x40, 45x45, and 50x50, using OpenOffice.org calc version 1.1. Recalculation times for each size were 3.5, 7.5, 21, 44 and 80s, respectively. The maximum memory consumption of the spreadsheet software was 32.4, 33.2, 33.8, 34.4, and 35.3 MB, respectively.

    Accuracy:
  In order to determine the accuracy of the calculations, we developed the following Octave / Matlab script to perform the same calculations.

  function val= timetest(sz, const)
  % TIMETEST: implement the spreadsheet timetest calculation
  % output_value = timetest( test_size, value_of_const )
  m= zeros(sz+2);
  m(2,2) = const;
  % set first two rows
  m(1,2:sz+2)= (0:sz);
  for i= 1:sz;
     m(2,i+2)= sin( m(1,i+1) + m(2,i+1) );
  end
  
  % set first two cols
  m(2:sz+2,1)= (0:sz)';
  for i= 1:sz;
     m(i+2,2)= cos( m(i+1,1) + m(i+1,2) );
  end
  
  % calc matrix inverses
  for i= 2+(1:sz)
      for j= 2+(1:sz)
         x= min(i,j)-2;
         im= inv( m( i+(-x:-1), j+(-x:-1) ) );
         m(i,j) = im(1,1);
      end
  end
  
  m(1,1)= im(1,1);
  val= m(1,1);
  

  Calculations were made for the following table sizes with const=2.0. The value shown is the (1,1) element of the largest matrix inverse calculated. Excel 2000 seems to have an inherent limit for matrices larger than 50×50. However, in this case, it seems that numerical inaccuracies accumulate such that sizes near 60×60 are totally inaccurate. All calculations are performed on a Pentium IV - 2.8GHz PC computer with 768MB RAM under windows XP, except those for Gnumeric and ATLAS optimized octave, which were performed on the same machine under Knoppix Linux. The first differing digit w.r.t. Matlab is shown in bold

  Software	Table size
  	40×40	50×50	52×52	57×57	60×60
  Calculated value
  Matlab (6.5)	0.535679883553	0.101149100585	0.197358954742	0.355983402713	0.291827646347
  Octave (2.1.57) Regular BLAS	0.535679913284	0.101148490613	0.197356215481	0.357508417492	0.371306450567
  Octave (2.1.57) ATLAS BLAS	0.535679801313	0.101158148119	0.197359432604	0.344510694995	0.685390442165
  Excel 2000 (9.0.2720)	0.535679811801	0.101153272416	0.197362580542	Unable to calc.	Unable to calc.
  OpenOffice Calc (1.1.2)	0.535679635577	0.101195007367	0.197348169676	0.291772731305	0.107339734386
  Gnumeric (1.1.17)	0.535679799858	0.101155910143	0.197361482313	0.351114401549	0.413445553387
  Calculation time (s)
  Matlab (6.5)	0.203	0.437	0.516	0.703	0.829
  Octave (2.1.57) Regular BLAS	0.770	1.400	1.532	1.978	2.306
  Octave (2.1.57) ATLAS BLAS	0.472	0.860	0.981	1.294	1.450
  Excel 2000 (9.0.2720)	1.75	4.70	5.51	Unable to calculate	Unable to calculate
  OpenOffice Calc (1.1.2)	19.88	70.06	91.81	140.00	174.06
  Gnumeric (1.1.17)	1.19	2.32	2.40	3.50	4.33