Formula | Prune inactive path

OAK's Formula | Prune inactive path command causes OAK to rewrite a formula so that it easy to understand how its result is derived. It does this by removing branches in conditional functions that are not active given current inputs.

For example, the expression IF(A1>B2,C3,D4) evaluates to either C3 or D4, depending on the result of the A1>B2.  Replacing the function with simply C3 or C4 leaves the spreadsheet giving the same answer as before, given unchanged inputs, and makes the calculation simpler and easier to understand.

Why would you want to use it?

Expression pares down a spreadsheet in order to understand the essence of a calculation.  It is particularly valuable in spreadsheets that make intensive use of OFFSET and INDIRECT; pruning will replace those terms with references expressed in simple coordinate notation which are very much easier to verify.

How to use it

What you could do if you didn't have OAK

The nearest thing to OAKs' Formula | Prune command is the Tools | Formula auditing | Evaluate formula command, which provides some facilities for probing a formula and understanding how it derives a result.

Wrinkles

Though the change that OAK makes to a spreadsheet leaves it delivering unchanged answers, that is only true so long as the inputs do not change.  Different assumptions might result in different paths being taken through conditional functions and different values being returned from lookup functions.  Under such conditions a pruned formula will give an answer that is simply wrong compared with the unaltered spreadsheet.

For this reason, it is essential that the OAK user keeps a copy of any spreadsheet that he or she applies OAK's pruning to.  The purpose of the pruning is to deliver a short lived spreadsheet that will be discarded after it has delivered insight into a particular calculation.

To be sure that OAK discards the right conditional paths and selects the wanted lookup values, ensure that the spreadsheet has been thoroughly recalculated before using this command.

Unlike many other commands in Excel and OAK, Formula | Prune does not follow the One-Cell rule, to prevent significant and possibly time consuming unintended changes to an entire worksheet when only one cell is selected.   To prune all the cells in a worksheet, select the whole worksheet first.