Selections suitable for reconstruction

The requirements

To be acceptable to OAK's Formula | Reconstruct command, a selection must be

•all or part of just one row of a spreadsheet

•be left-right consistent, that is, contain formulas that are all copies of each other.

In addition, if OAK is asked to reconstruct only the paths in a formula that are active, then what is acceptable becomes more restricted, though it is just a special case of the foregoing requirements: the selection must consist of a single cell.

It is permissible for the cells being reconstructed to form an array, so long as the entire array is selected (and the above requirements have also been met).

motivation

These requirements are all driven by the same consideration: reconstructing formulas is a potentially expensive thing to do computationally, particularly where formulas have a large degree of fan-out.

To deliver the reconstructions in a reasonable time, OAK does not reconstruct every cell in the selection.  Instead, it reconstructs the first cell and the last cell in the selection only, and fills in the intervening cells by copying the reconstructed formulas.

In principle, having established that a selection is left-right consistent, it would be sufficient to reconstruct just the first cell, and to fill in the others by copying the resulting formula.  OAK reconstructs the first and last cell, and checks that they give the same formulas (in RC notation).  Only if they match does OAK deliver the requested reconstruction.  This is consistent with Operis's strong belief that the result of a calculation is more dependable if it can be derived using two independent methods.

If the two calculations don't match, OAK will report that it has encountered an error.  Many months of testing have passed since OAK reported such a mismatch.  Neverthless, since such errors are the result of incorrect programming, Operis would like to know about any that users encounter, so that it can remove them from future versions of OAK.

When OAK is instructed to reconstruct only the active paths in a formula, it prunes the expressions it encounters.  There, for example, =IF(condition,truepart,falsepart) will be turned into =truepart, or =falsepart, depending on the result of evaluating condition.  In this way, depending on the inputs, two neighboring cells that have identical formula before the pruning may have different formulas after the pruning.  OAK would therefore no longer be able to take the short cut of reconstructing only the first and last formulas selected and relying on copying those to fill any intervening cells.  For this reason, OAK will only offer the option to limit the reconstruction to the active paths in a formula if the selection consists of a single cell.