3 ms·
How does this normalization affect being able to repeat formulas (or references in formulas)?
by networked 7y ago
How does this normalization affect being able to repeat formulas (or references in formulas)?
- microcolonel 7y agoWhile spreadsheets usually display references as though they refer to a specific cell (i.e. A3, B2, etc.), but underneath, the references are relative (unless specifically made absolute, with $ in the case of A1). The common pattern in spreadsheets is to have a set of columns of repeated formulas. i.e. | A | B | C | D | |-----|-----|----------------|-----| 1 | | | | 0.12| 2 | 42| 42| =A2*B2+C1*$D$1 | | 3 | 69| 69| =A3*B3+C2*$D$1 | | Where, you'll note, although the function and reference shape in C2 and C3 is identical, the text is not. Whereas, with R1C1-type references. | 1 | 2 | 3 | 4 | |-----|-----|----------------------------|-----| 1 | | | | 0.12| 2 | 42| 42| =RC[-2]*RC[-1]+R[-1]C*R1C4 | | 3 | 69| 69| =RC[-2]*RC[-1]+R[-1]C*R1C4 | | The text of the formula is exactly the same in both copies. This makes it a lot cheaper to deduplicate them, because we don't need to run the whole parser on the 400k+ formula invocations in our sheet, and then compare the ASTs rather than text; since in this form, there are only a few thousand unique expressions rather than a few hundred thousand.
- networked 7y agoThanks for the explanation. I was confused about the meaning of "repeat". It's a missed opportunity that ODS doesn't store formulas as ASTs in the first place.
- microcolonel 7y ago> It's a missed opportunity that ODS doesn't store formulas as ASTs in the first place. It's really for the best that they don't. ODS is XML, so they'd probably make the AST XML as well, which would outrageously oversized.