Expression pruning in fan out precedents

Top  Previous  Next

A formula of the kind

=VLOOKUP(A1:Z1000,"London",3)

makes reference to 26,000 cells and every one of them would be listed if such a formula was encountered in a discrepancy analysis.  And, depending on how many levels of precent OAK had been instructed to report, each of those 26,000 cells would have precedents, possibly many of them, which OAK would also attempt to list.

The fan-out in the resulting report would be enormous.  And quite useless.  Though the VLOOKUP may mention thousands of cells, it will always evaluate to a single value corresponding to just one of them.

OAK has the capability of confining a discrepancy analysis to the one useful cell.  It does this by subjecting formulas that it encounters to the same kind of expression pruning as is used in OAK's Formulas Optimize command.  An analysis that may be thousands of cells without pruning can be reduced to a few dozen cells with it.

Which functions are to pruned are indicated half way down the discrepancy analysis dialog and can be specified by pressing the button to the right of the list.

OAK5FanOutPrecedents2

Tip: If two calculations differ because one is taking the TRUE branch of an IF and the other is taking the FALSE branch, you will likely not want to prune away the IF function.  But if they follow the same branch of the IF, but nevertheless deliver different results, pruning out the IF may well be helpful.