3 ms·

Kind of orthogonal questions on spreadsheets. Does anyone know any good resources describing how the spreadsheet software is actually built? Especially the par

Kind of orthogonal questions on spreadsheets. Does anyone know any good resources describing how the spreadsheet software is actually built? Especially the part where formulas are written using column and row numbers but when columns/rows are added or removed, formulas are updated and remain in sync?

RodgerTheGreat 4y ago
One facet of the apparent complexity comes from the fact that Excel's default "A1" cell indexing style is actually a facade for "R1C1" style references, which can express relative cell coordinates. A column of identical R1C1 references appear to change when you add or remove rows and columns around them because they're being shown as A1.
- HPsquared 4y ago
  This, and updating of values when a cell is updated. Formula evaluation is based on a directed acyclic graph. If a node on the graph is updated, everything "downstream" gets updated but the formulas "upstream" don't get re-evaluated.
  - rad_gruchalski 4y ago
    I read about the usage of dag in this problem. But my question is how are the actual cells referenced in the formula. As a user, I type =B12C34, then add two columns after A and 5 rows after row 5. The formula is now =D17E39. If I add 2 rows at row 20, the formula becomes =D17*E41. The offset-based approach is what I considered to be the most optimal so I’m happy to see that mentioned. So a complete solution would be a dag of offsets? A native internal representation would store something like: (col 4, row 17) operator (offset col +1, row +24).
i_don_t_know 4y ago
I don’t know if these books address your specific question, because I haven’t read either one. Peter Sestoft has looked at spreadsheet implementations: Spreadsheet Technology http://www.itu.dk/people/sestoft/funcalc/ITU-TR-2011-142.pdf http://www.itu.dk/people/sestoft/funcalc/ITU-TR-2011-142.pdf Which seems to cover similar topics as his book: Spreadsheet Implementation Technology https://mitpress.mit.edu/9780262526647/spreadsheet-implementation-technology/ https://mitpress.mit.edu/9780262526647/spreadsheet-implement...
- warpech 4y ago
  Spreadsheet Implementation Technology is really solid
- rad_gruchalski 4y ago
  Thank you, this is great.

RodgerTheGreat 4y ago

One facet of the apparent complexity comes from the fact that Excel's default "A1" cell indexing style is actually a facade for "R1C1" style references, which can express relative cell coordinates. A column of identical R1C1 references appear to change when you add or remove rows and columns around them because they're being shown as A1.

HPsquared 4y ago
This, and updating of values when a cell is updated. Formula evaluation is based on a directed acyclic graph. If a node on the graph is updated, everything "downstream" gets updated but the formulas "upstream" don't get re-evaluated.
- rad_gruchalski 4y ago
  I read about the usage of dag in this problem. But my question is how are the actual cells referenced in the formula. As a user, I type =B12C34, then add two columns after A and 5 rows after row 5. The formula is now =D17E39. If I add 2 rows at row 20, the formula becomes =D17*E41. The offset-based approach is what I considered to be the most optimal so I’m happy to see that mentioned. So a complete solution would be a dag of offsets? A native internal representation would store something like: (col 4, row 17) operator (offset col +1, row +24).

i_don_t_know 4y ago

I don’t know if these books address your specific question, because I haven’t read either one. Peter Sestoft has looked at spreadsheet implementations: Spreadsheet Technology http://www.itu.dk/people/sestoft/funcalc/ITU-TR-2011-142.pdf http://www.itu.dk/people/sestoft/funcalc/ITU-TR-2011-142.pdf Which seems to cover similar topics as his book: Spreadsheet Implementation Technology https://mitpress.mit.edu/9780262526647/spreadsheet-implementation-technology/ https://mitpress.mit.edu/9780262526647/spreadsheet-implement...

warpech 4y ago
Spreadsheet Implementation Technology is really solid
rad_gruchalski 4y ago
Thank you, this is great.