There’s been a Logic Map function in Rainbow for some time, showing you very useful information about which cells are inputs (i.e. other calculations depend on them, but they themselves are not calculated), which cells are workings (i.e. they are calculated, and other calculations depend on them), and which cells are outputs (i.e. they are calculated, but no other calculations depend on them), as well as which cells are labels (i.e. they are not involved in any calculations). But until now the Logic Map function has always been so slow that you could only use it on the smallest of worksheets or workbooks.
What has changed is that we’ve now developed a completely new algorithm for generating the Logic Map, which runs about 100 times faster than the old one. On one of our larger test workbooks it completed the Logic Map in under 90 seconds, whereas the old algorithm was still running after 3 hours! The new algorithm works by constructing a model of the entire workbook logic in your computer’s internal memory, and then using this to highlight cells in the appropriate Logic Map colours.
As a very basic example, here (on the left) is a simple table showing stock values. At first glance it appears well-constructed, with no immediately obvious problems. But the Logic Map function tells you a very different story, as you can see in the composite screenshot above.
The Logic Colours key (on the right of the screenshot above) shows you what the different colours mean, and you can see that:
- Cells G1:G2 (highlighted red) are acting as input values to a formula, which is clearly wrong. And conversely cells G5:G6 are not used at all, which is why the 10% in G6 is shown as a label (magenta) rather than an input (yellow). The reason is then obvious when you look at the formula in cells H5 (visible in the formula bar) and H6 – there is an incorrect reference to cell G1 instead of G5. This is also why the value in cell H6 ignores the 10% discount.
- The SKUs and descriptions in columns A:B are mostly output values (highlighted cyan) calculated via a formula reference from another worksheet. But the values in A6:B7 are simply labels (magenta) which have been typed in, evidently overwriting the formulas. The typed-in values may be incorrect, and clearly need to be investigated.
- The values in cells D12:E13 are highlighted as labels (magenta) rather than inputs (yellow), indicating that they are not used in any formulas. And the reason for this is clear from cells F12:F13, where the zero values have been typed in as inputs (yellow) rather than calculated (green) from D12:E13. The Logic Map function is ideal for detecting this type of error involving unused “input” cells (D12:E13), which may not show up in other Rainbow functions such as Formula Scan.
- In column G some of the discount values are typed in as inputs (yellow) and some are calculated (green). This may not be an error, but it should at least be checked.
Of course our example is designed to show off the Logic Map function, but hopefully you can see how in any spreadsheet it can highlight unused inputs and other logical inconsistencies that could be hard to detect without it. And because the new super-fast algorithm makes it easy to generate a Logic Map for almost any spreadsheet, we anticipate that some Rainbow users will want to make this a standard part of their spreadsheet audit process.
This is why we’ve slightly rearranged the Rainbow menu to show the Logic Map icon on the top row, and we have now included this in the Initial Overview group of functions. But all of Rainbow’s existing functions are still there (click Menu Guide to see them), and we’ve still retained the old Logic Map algorithm for the very few cases where it may be needed (see the User Guide for details), making the Logic Map a very powerful and flexible audit function. As far as we know Rainbow is the only spreadsheet audit tool to offer this Logic Map functionality, and we believe it adds significantly to Rainbow’s audit capabilities.