Mixed levels

Expansion not always possible

Though one might ask for a reconstruction that involves a certain number of levels of precedent substitution, OAK may not deliver it.

The most obvious reason is that there are not that many levels of precedent in the model.  For example, no precedent substitution is possible in a reconstruction of the formula =F1+F3+F5  if F1, F3 and F5 contain constants.

The second reason is that some functions mention large ranges.  The formula =AVERAGE(A1:Z1000) refers to 26,000 cells.  Putting every one of those in a reconstruction would be infeasible.  OAK applies special treatment to such functions which in many cases results in their precedents not being expanded to the next level of precedent.

A third reason is that a formula may refer to another workbook.  If that workbook is open, OAK will follow the link, and continue the precedent substitution.  But if the workbook is not open, OAK will not expand a reference to the cell containing the link.

Levels can be mixed

In the formula =F1+F3+F5, we could imagine that

•F1 contains a constant.

•F3 contains the formula =AVERAGE(A1:Z1000).

•F5 refers to G5, which in turn contains =H5+I5+J5

In such a case, OAK will generate a reconstruction equivalent to =F1+AVERAGE(A1:Z1000)+H5+I5+J5

•The F1 terms will be settled when OAK considers level 0 of the reconstruction.  That cell contains a constant, which is not amenable to further precedent expansion

•The AVERAGE(A1:Z1000) term will be settled when OAK considers level 1 of the reconstruction, and made the substitution of that term for F3; but since it mentions thousands of cells, OAK will attempt no further expansion.

•The H5+I5+J5 subexpression will be settled when OAK considers level 2 of the reconstruction, and made the substitution of that expression for G5, which is what  it put at level 1 in place of F5.

So though this would purport to be a Level 2 reconstruction, it actually has a mixture of terms that were decided at levels 0, 1 and 2.

It's a maximum

The control on the dialog box presented by OAK when the Formula | Reconstruct is invoked should be thought of as controlling the maximum number of levels of precedent substitution that will be made.  What OAK delivers may contain a mixture of levels within the same reconstructed formula, up to that maximum.

If OAK runs out of opportunities to make precedent substitutions before the specified limit, it will deliver fewer levels of reconstruction than asked for.  For example, if OAK can make no further precedent substitutions after Level 4, OAK will deliver a Level 4 reconstruction, even though it might have been asked for a level 8 reconstruction.