Item11450: SpreadSheetPlugin $ABOVE() thinks rows are zero-based

I put just $ABOVE() in R8:C13 cell and it returned R0:C13..R8:C13. Looks like ABOVE thinks rows are zero-based.

gac410 confirmed that $ABOVE() has a unit test, but the test itself is looking for the wrong value.

-- DavidKramer - 17 Jan 2012

Added unit tests. Also noticed that $LEFT() was hard coded to C0, so fixed that. Added a unit test for SUMPRODUCT() and a test to combine $SET() and $GET() in a single cell. Released as SpreadSheetPlugin 1.1.5

-- GeorgeClark - 17 Jan 2012

Breaks unit tests for EditTablePlugin. But also see in the unit test that some other macro is returning R0 for first row. ... I don't think that R0 is a bug however. Macros relative to a table position when expanded outside of a table have Row 0.

-- GeorgeClark - 17 Jan 2012

If the first row is R1, how is anything returning R0 inside a table NOT a bug? It certainly prevented my table's calculations from working right by returning an invalid range. When I replaced $ABOVE() with a hand-coded range, it worked.

As a user who appreciates the principle of least astonishment, I would rather see ROW(), COLUMN(), ABOVE(), LEFT(), RIGHT(), etc return an error when called outside a table. Silently returning a bogus value when a function is called in an illegal way is a disservice to the user, especially in a non-procedural environment where the user can't easily check return values.

-- DavidKramer - 17 Jan 2012

I'm just referring to when used outside of a table. R0 inside the table is fixed. Verified by the unit tests. The EditTable unit tests assigned a %CALC to a Set = variable. When the * Set statement expands inline, it shows R0, but the expansion within the table returns the correct information.

The SpreadSheetPlugin is very old and not something I want to change beyond clearly identified errors where it generates incorrect results when used correctly. Anything beyond that is probably asking for troubles. It really needs a rewrite.

-- GeorgeClark - 17 Jan 2012

Commenting is disabled while not logged in