Behaviour
Though one might ask for a reconstruction that involves a certain number of levels of precedent substitution, OAK will not always deliver it.
One subtle reason for this is that OAK seeks to reconstruct a calculation, which it defines as a single cell, or several neighboring cells in a single row that are left-right consistent, that is, have the same formula through being copies of each other.
OAK enforces this requirement before it starts the reconstruction process. If the cells selected for reconstruction do not contain consistent formulas, OAK will decline to reconstruct them.
It is possible that the initial cells will be left-right consistent, so OAK finds them acceptable for reconstruction, but then discovers that at some point the precedent cells are not consistent. In such cases, the rule is simple: OAK will not deliver a reconstruction that is not left-right consistent. OAK will halt the process of precedent substitution in the affected branch of the calculation, though it will continue in other branches of the calculation that are consistent.
Example
The screenshot below shows a spreadsheet in which all rows are left-right consistent between columns D and G. Each row has been annotated in column I with the formula in column G.
If row 15 is reconstructed, at level 0 the result simply reproduces the calculation's action of combining the fixed and variable costs.
At level 1, the calculation is expanded and shows clearly that the fixed and variable costs in nominal terms are the result of inflating the same costs in unescalated terms.
But if, now, we change cell G14 by remove the dollar sign from the formula, it will no longer be an exact copy of its neighbors, though it will still give the same result.
A reconstruction of row 15 gives the same result at level 0, again simply demonstrating the calculation's action of combining the fixed and variable costs.
But at level 1, the result is different. The term for the fixed costs has been expanded, to show that it is derived by inflating the unescalated version of those costs . But the term for variable costs has not been expanded. It is still referring to the nominal version of those costs, because OAK has detected that further to expand that branch of the formula would result in something that would no longer be left-right consistent, and so cease to be a calculation by its definition.
Applications
Often, the delivery by OAK of a reconstruction that stretches less far back through the hierarchy of precedents than expected is the first clue that a calculation relies on unintentionally inconsistent formulas at some point in the process. That inconsistency can be identified
•by having Excel find inconsistent formulas using Edit | Go To | Special | Row differences
•by looking for the small green indicators that Excel shows in the upper left corner of cells that appear suspect. (These are of at least some use, but they are easy to miss in a big spreadsheet, and didn't show at all in the example above, because they appear when an inconsistent cell is in the middle of a run of other cells, but not when it is at the end of the run as here.)
•by having OAK map the workbook
•by using Ctrl+[ (left square bracket) to follow the links between the reconstruction and those of the precedent cells of the calculation being reconstructed that one expected to be expanded by further precedent substitution.