Inconsistent Formulas

From time to time, I am asked to audit a spreadsheet.

One of the common mistakes I see is an inconsistent formula inside a group of cells.
For example:
– A1 has the formula =G1 * 0.105
– A1 has been formula auto-filled to A1:E5
– Then some time later, cell A3 has been changed to =G3 * 0.107

So the assumption is that A1 can be safely auto-filled to the region of cells. However, we have an exception which should be noted.

It’s nice to be able to highlight these exceptions. Here are 2 ways.

1. If you have Excel XP and above, you can use the Error Checking options. A little green triangle appears telling you that “something strange” is going on.
One downfall of the Error Checker is that it wont trigger if the inconsistent formula is on the corner of the checked region. In our example above, if the changed formulas was A5 instead of A3, it would have ignored that inconsistency.
I assume that this is to accommodate subtotals and grand totals?

2. Write a bit of VB code using the idea that the R1C1 version of the formula should be identical for all cells in the selection, so it should be a simple loop to check all of the formulas.
Here is an example:

Sub test()
Dim strFormula As String, rng As Range

strFormula = Selection(1).FormulaR1C1

For Each rng In Selection
If rng.FormulaR1C1 <> strFormula Then rng.Interior.ColorIndex = 6
Next
End Sub

To use it, select the range A1:E5 then run the macro. It would colour the inconsistent formulas yellow (in the first example, cell A3).

3 thoughts on “Inconsistent Formulas”

From section 3.3.3 of my book: “Correct inconsistencies in a pattern of formulas. “
In Excel 2000 and later you can select the range you want to test and then use the Edit > Go To > Special menu command with these options:

Row Differences:Cells in the same row as the active cell, whose contents are different from the active cell. Shortcut: Ctrl+

Column Differences:Cells in the same column as the active cell, whose contents are different from the active cell. Shortcut: Ctrl+Shift+|

If more than one cell is found, they are shaded as a multiple selection and you can use the Tab key to visit each of them.

Patrick O’Beirne
‘Spreadsheet Check and Control’ ISBN 190540400X