LibreOffice Calc: Flatten formula dependencies

user_12264923 0
Viewed 155

I want to create a cell formula that is equivalent to flattening the dependencies of several other cell formulae. Trivial example:

  • A1 = 10
  • A2 = 5
  • B1 = A1 + A2
  • B2 = A1 / A2
  • C1 = B1 - B2

'Flattening' C1 gives C1 = (A1 + A2) - (A1 / A2). A1 and A2 being the only cells that have values and therefore no dependencies on other cells.

Motivation: Feeling your way through a calculation by building up a bunch of intermediaries and then condensing them into a single cell formula.

libreoffice-calc formula dependencies
1 Answers

After protracted searching I had to bake my own solution. Python macro to tokenize the starting formula and then expand the precedents into the formula stopping at cells that contained values, ranges or references to other files. Solution is a little long winded so I created a git repo with the macro in it: https://github.com/ptolch/LibreOffice/tree/master/macros

user_122649230
Related