Formula Checking

Formula Scan

When it comes down to detailed formula-by-formula logic checking, Rainbow provides all the tools you need. The Formula Scan function highlights blocks of identical (copied) formulas in the same colour, enabling you to spot immediately any inconsistent formulas. You can see in the screenshot above that cell D9 has a different colour (i.e. a different formula) from the cells on its right, and cells G13 and H13 have a different colour (formula) from the cells on their left, confirming what we noticed when analysing this spreadsheet with the Logic Map function.

Formula reportIn addition, you can see above that cell C22 has a different colour (formula) from the cells on its right, and you can easily compare the two formulas in this colour-coded report generated by the Formula Scan function. The report lists each formula block, so you can check each formula just once and then mark it as checked (in column G). And the report has a hyperlink for each formula (in column C), so that you can click to jump straight to the relevant cell being analysed, and then return to the report with Alt+Tab.

The error in cell C22 was not picked up by the Logic Map function, because both cell C22 and the cells on its right are intermediate calculations with one dependent. So you can see that the Logic Map function and the Formula Scan function complement each other; some of the errors they highlight will be the same, but others will be different (and for instance the Logic Map function highlights information about input cells that you cannot see with Formula Scan).

Cell InspectorTo help you with checking individual formulas, Rainbow provides the Cell Inspector function, which as shown here gives you:

  • a description of what the current cell appears to mean,
  • a list of all its precedents and dependents, also with descriptions,
  • interactive navigation through the logic tree,
  • a parser (structured layout) to help to simplify complex formulas.

When you double-click on any of the cell’s precedents or dependents (or select it and click the relevant button), Cell Inspector takes you straight to the actual cell in the underlying spreadsheet, so that you can see the flow of logic unfolding on the screen. And you can click the back button to return step by step to the original cell.

As you probably know all too well, detailed logic checking is by far the most time-consuming part of the audit process, but with Rainbow’s functions you can reduce this by many hours.

Back to topAdministration