OAK's Formula | Reconstruct command causes OAK to generate a new workbook which contains one or more worksheets. Each worksheet sets out a report which seeks to make it easy to understand how a calculation derives a result.
The formula =A1000+B2000+C3000 is hard to understand because the cells referred to are far apart on a worksheet. The formula =Sheet1!AA1000+Sheet2!BB2000+Sheet3!CC3000 is even harder to understand because the cell referred to are widely separated in three dimensions. A reconstruction of a calculation shows what it would look like if those cells that play no part in the calculation were removed from the spreadsheet, so that those cells that are involved could be repositioned near to the formula.
The ability to reconstruct calculations is one of the most distinctive and technically ambitious capabilities of OAK and is explained in detail in the section of this help that sets out Basic concepts in OAK.
When the command is invoked, OAK checks that the selection is suitable for reconstruction.
| • | A single area |
| • | All or part of one row |
| • | Same formula in each cell. |
If it is not suitable, OAK presents a suitably descriptive message of complaint.
If it is suitable, OAK presents a dialog box.
The address of the selection is shown in field Row to reconstruct at the top of the dialog. If you realize that is not the range you want to reconstruct, press Cancel to dismiss the dialog, change the selection and try OAK4 | Formula | Reconstruct again.
Since a well-written calculation will be built up in several cells, each with a formula calculating a piece of the result, OAK attempts to show the entirety of the calculation by combining the formula in a cell with the formulas in the cells it mentions. This action can be controlled by setting the Maximum number of levels of precedent formula substitution. A low number will quickly produce a reconstruction that may describe only part of a calculation. A high number will take longer to produce a reconstruction that may have a long formula that is hard to read. A happy medium may lie between these extremes. As an aid to finding that midpoint, OAK can be instructed to Show all levels up to limit, in which case it will produce a workbook with worksheets showing levels of formula expansion from zero (no formula expansion: only the selected formula is reconstructed) up the limit specified.
When Confine to additions is switched on, OAK will combine the formula in a cell with the formulas in the cells it mentions, up to the number of levels of precedents specified, but only to the extent that the result uses the operators +, - and SUM. This is useful for generating the components of a grand total without any intervening subtotals.
OAK's pursuit of insight into the whole of a calculation by combining formulas can produce results that are long and complicated. Two options are offered to address this.
| • | OAK can be instructed to Optimize the formula that it develops in reconstructing a calculation, that is, to try to simplify it without changing its effect. This can be valuable when the reconstructed formula becomes very long. The action is the same as that performed by OAK's Formula | Optimize command. |
| • | OAK can be instructed that a reconstruction should be Confine[d] to active path. It will reduce conditional and lookup functions to their results, eliminating the need to include in the reconstruction branches and values that are not activated by current inputs. The action is the same as that performed by OAK's Formula | Prune command. The various relevant functions can be selected or deselected for pruning individually. |
Several controls on the dialog influence how a reconstruction report is displayed.
| • | OAK can be instructed to give an indication of where in a worksheet the cells that are mentioned in a formula are located, by showing additional Rows of context either side of rows containing cells that participate in a formula. The context rows are shaded grey so that they appear fainter, to signal that they do not participate in the calculation, but are there for information. |
| • | OAK can be instructed to reproduce any timeline or similar dimensional information by reproducing the frozen rows of the window in which the reconstructed worksheet is displayed on the screen, or the print titles set for printing the worksheet. Since these resemble the rows of context, in aiding interpretation of the formula, but not participating in it, they too are shaded grey. |
| • | OAK will freeze a specified number of columns at the left of the reconstruction sheet so that they remain visible when the rest of the spreadsheet is scrolled. |