Messy
Because the process of reconstructing calculations involves replacing references to precedents with those precedents' formulas, reconstructions can get long and messy.
By messy, we mean that the process of substitution can give rise to expressions that no one would write naturally. For example,
•if |
cell A1 has the formula =B2 + C2 |
•and |
C2's formula is = - D3 |
•then |
the result of substitution will be =B2 + - D3. |
Of course, a more natural formula would be =B2 - D3.
Transformations
OAK is capable of addressing this problem by applying a range of mathematical transformations to simplify reconstructed formulas as they are developed. The process is identical to the one used by OAK's Formula | Optimize command. The transformations used are listed at Simplification rules.
Some of the simplification rules are safe; there are no circumstances under which they alter the meaning of a formula. Others work in most circumstances, but there are obscure conditions under which they will alter the effect of a formula. OAK can be instructed to
•optimize the reconstructed formula aggressively, that is, to apply all the simplification rules
•optimize the reconstructed formula conservatively, that is, to apply only the simplification rules that are safe in every circumstance
•not to do any optimizing.
If OAK delivers a reconstruction that shows a non-zero discrepancy, the first thing to try is a move from aggressive to conservative optimization.
Length
These transformations don't only control the cosmetics of a reconstruction. They are also helpful in taming the length of the resulting formula, which may become extreme due to fan-out of the precedents whose formulas are substituted.
For example,
•if cell A1 contains the formula =IF(B2<0,0,B2)
•andcell B2 contains the formula =C3+D3+E3+F3+G3+H3+I3+J3+K3+L3
•then the reconstructed formula, after precedent substitution, will be
=IF(C3+D3+E3+F3+G3+H3+I3+J3+K3+L3<0, 0, C3+D3+E3+F3+G3+H3+I3+J3+K3+L3)
But one of the transformation rules applied by OAK is that =IF(a>b,a,b) becomes MAX(a,b).
Applying this turns the formula into =MAX(0, C3+D3+E3+F3+G3+H3+I3+J3+K3+L3), which is very much shorter.