INTRODUCTION
Understanding which cells a formula refers to, and coming to a view on whether they are the correct cells in the circumstances, is a central activity in checking a spreadsheet.
Excel offers several helpful indications of these relationships between these cells, which are described in this help under Finding cell precedents in Basic Excel Concepts in this help.
The problem
Imagine you have a formula =E3000+E6000+E9000 .
Whatever cell that formula happens to be in, it's hard to see what quantifies it is adding up, because they are thousands of rows apart.
There are tricks, involving just a handful of keystrokes, which make use of the methods built into Excel for finding cell precedents, which can help decipher this formula. But the tricks will only work when all the cells are on the currently active worksheet. The formula =Sheet1!E3000+Sheet2!E6000+Sheet3!E9000 is a fiddle to decipher using methods built in to Excel.
The solution
This problem is so common that Operis has sought to address it by including relevant facilities in OAK. The most ambitious feature of OAK is the ability to generate a reconstruction of a formula. A reconstruction is a new worksheet which lays out the essence of a calculation as simply as possible.
First example
The easiest way to understand a reconstruction is to try one.
ACTION |
COMMENTARY |
||
|
|
||
|
The easiest way to do this is to press Ctrl+[ while cell C5 is selected, and visit the three cells mentioned in the formula in turn, filling them in and pressing TAB to move on to the next one. |
||
|
The easiest way to do this is the press F5, then enter. |
||
|
A dialog will appear, with lots of options. These will be explained later in this help. For now, accept the defaults, and press OK. |
OAK will generate a new workbook, like the one below.
Cell F2 in the new workbook has the formula =[Book1]Sheet1!F9, which links it to the calculation that has been reconstructed.
Cells E10, E12 and E14 in the new workbook have the formulas =[Book1]Sheet1!E3000, =[Book1]Sheet1!E6000 and =[Book1]Sheet1!E9000, which link to the cells that were referenced by the calculation that has been reconstructed.
Tip: The cell linked to by any of these four formulas can be seen in its original context by selecting the formula, then pressing Ctrl+[. You can return to the reconstruction report by pressing F5, then OK or Enter.
Cell F3 in the new workbook is OAK's reconstruction of the original formula. It is derived by taking the original formula, =E3000+E6000+E9000, and adjusting it so that it refers not to those cells directly, but to those cells indirectly via cells E10, E12 and E14 further down the page.
Cell F4 compares cell F3, the reconstruction, with cell F2, the link to the original cell, to provide reassurance that the reconstruction has successfully reproduced the original calculation. The discrepancy should be zero. If OAK generates reconstructions where the discrepancy is not zero, Operis would like to know about them.
Interpreting the reconstruction
The original formula, =E3000+E6000+E9000, involved cells that were thousands of rows apart, which makes it hard to review and understand. The reconstruction restates the calculation so that it involves cells that are just a few rows further down the worksheet, which makes it easy to review and understand.
The formula subject to reconstruction was placed in cell F9. As a result, the link to the cell containing that formula, OAK's reconstruction of it, and the discrepancy between them are all positioned in column F of the reconstruction worksheet.
The cells mentioned in the formula subject to reconstruction were in cells E3000, E6000 and E9000. As a result, the links to those cells in the rows 10, 12 and 14 of the reconstruction worksheet are all positioned in column E.
The fact that the formula is in a different column from its dependents is hard to notice when the cells are many rows apart, as they are in the original spreadsheet, but very much more obvious from the reconstruction. Whether such a misalignment is intentional or unintended would be an appropriate subject for further investigation in a real model review.