Confine to Active Path
Reconstructing a formula =CHOOSE(A1,B2,C3,D4,E5,F6) will result in OAK digging back through the precedent hierarchy of all six of the cells mentioned, and producing a potentially long reconstruction formula.
Sometimes that is exactly what is wanted. But more often, it's not useful to have a reconstruction showing the details of all the paths, when only one is active. The point of a reconstruction is to elucidate the operation of a calculation, and that is more effectively done with all superfluous detail pared away.
OAK can do this. It provides facilities for stripping out of reconstruction formulas all references to cells that are not actually active at a particular moment. The action is identical to the expression pruning offered by OAK's OAK4 | Formula | Prune command, and the relevant option in discrepancy analysis. It is the most powerful weapon against the tendency of fan-out to cause reconstructions to swell beyond an insightful size.
OAK reconstructs a calculation, which it defines to be neighboring cells with identical formulas. Two formulas that are identical before pruning may not be identical after pruning: one may take the TRUE branch of an IF(...) while the other takes the FALSE branch. For this reason OAK's facilities for pruning a reconstruction, labelled "Confine to Active Path", are enabled only when the range selected for reconstruction is a single cell.
Special cases of reconstruction pruning
Often, the calculation underlying the build up of a result in a model is simple. Revenues and costs are typically built up as quantity x unescalated price x inflation escalator. Yet the formulas that implement this calculation can be complicated, as they switch between different scenarios, look up the relevant inputs in tables, and cause revenues and costs to start and stop, or ramp up and down, according to specified timing. Switching on the pruning can often deliver reconstructions of the essential, underlying calculation.
Where OAK is asked to reconstruct a formula involving HLOOKUP and VLOOKUP without pruning, the entire lookup table will be shown in reconstruction. That may well be useful if the goal is to check the working of the lookup. With pruning turned on, just the cell looked up will appear in the reconstruction formula. Similar considerations apply to INDEX and LOOKUP.
The OFFSET and INDIRECT functions are unusual in that the cells they resolve to need not have anything to do with the cells mentioned as function arguments. =OFFSET(A1,1000,0) refers to cell A1001, not to cell A1. OFFSET could refer to any cell on a worksheet; INDIRECT could refer to any cell on any worksheet. (Operis considers them dangerous, and instructs its analysts not to use them.) There is a high chance that a reconstruction of a formula that uses either of these functions won't work, as evidenced by delivery of a report with a non-zero discrepancy. Options in such cases are
•to specify pruning when initiating the reconstruction
•to turn up the number of lines of context that are displayed in the report, in the hope that that will cause the offset rows to appear on the reconstruction report among the lines of context
•to use OAK's Formula | Prune command on the original model (keep a copy of it!) to prune out the OFFSET or INDIRECT functions before starting the reconstruction.
This last option leads to a more general point: it is often insightful to use OAK's Formula | Prune and Formula | Reconstruct commands together:
•optionally, prune away some complexity from a spreadsheet using OAK4 | Formula | Prune
•reconstruct the result
•then do further pruning of the reconstruction report.