Exceeding Excel bounds in excel format export
Submitted by Harlan Grove
Both Gnumeric 1.4 and OpenOffice Calc 1.1.3 allow entry of formulas that go beyond Excel's capabilities. For example, with all cells in A1:A40 initially containing the formula =ROW(), both allow entry of the formula
C1: =SUM(A1;SUM(A2;SUM(A3;SUM(A4;SUM(A5;SUM(A6;SUM(A7;SUM(A8;SUM(A9;SUM(A10; SUM(A11;SUM(A12;SUM(A13;SUM(A14;SUM(A15;SUM(A16;SUM(A17;SUM(A18;SUM(A19; SUM(A20;SUM(A21;SUM(A22;SUM(A23;SUM(A24;SUM(A25;0)))))))))))))))))))))))))
24 levels of nested function calls
C3: =SUM(A1;A2;A3;A4;A5;A6;A7;A8;A9;A10;A11;A12;A13;A14;A15;A16;A17;A18;A19;A20; A21;A22;A23;A24;A25;A26;A27;A28;A29;A30;A31;A32;A33;A34;A35;A36;A37;A38;A39;A40)
In isolation, this is good. However, when OpenOffice Calc saves workbooks containing such formulas in Excel 97/2000/2002/2003 .XLS format, it saves the C1 formula but not the C3 formula. Gnumeric saves both. 'Saves' in the sense that when such .XLS files are opened in Excel, the original formula appears in the formula bar.
While Excel can't modify these formulas without them being truncated to 7 levels of nested function calls and 30 arguments, Excel does evaluate the C1 formula saved by OpenOffice Calc. The C3 formula is replaced with =#N/A. If the values in A1:A25 change, so does the calculated value of C1 in Excel. So it appears Excel can evaluate formulas involving more than 7 levels of nested function calls created in OpenOffice Calc.
For the .XLS workbook saved by Gnumeric, when opened in Excel and fully recalculated ([Ctrl]+[Alt]+[F9]), all formulas evaluate to #VALUE!, including the =ROW() formulas in A1:A40. Selecting A1:A40 and pressing [F2] and [Ctrl]+ [Enter] in sequence makes A1:A40 return 1..40 again, and C1 evaluates again. C3 comes through as the same formula entered in Gnumeric, but evaluates to #VALUE!.
From my perspective, it's probably a good thing the C1 formula is saved in .XLS format files by both Gnumeric and OpenOffice Calc since Excel can calculate it even if it can't modify it. Whether the C3 formula should be saved is problematic. However, it is a bug that the =ROW() calls in A1:A40 recalculate as #VALUE! in Excel and need to be re-entered. This behavior also occurs when each of the formulas in A1:A40 is entered as single-cell array formulas then saved in .XLS format.
So there appears to be a bug in how the ROW() function is saved in .XLS files.
Version: git master