Fourth article in Error Handling (Background Error Checking or Green mark) series. Read these articles first to maintain continuity.
Auditors, Risk Managers, Everyone: Did you know? These green marks are WARNINGS!!
Green Marks Part 2: Formulas showing wrong results!
Green Marks Part 3: More error handling
In this article, we will cover two more potential errors related to Inconsistent Formulas.
Contents
Inconsistent Formulas
This is the data we have. Amount and Tax. The tax is 2.5%. We already have a formula in column B to calculate the tax.
Now, for whatever reason, the tax in row 7 has to be changed to 2.6 %. When that formula is changed, see what happens… (Showing formulas instead of results for clarity). Error mark is added. This is a different type of error. It says Inconsistent Formula.
Excel is constantly monitoring all these things in the background. That is why it is called Background Error Checking. When it detects such a discrepancy, it wants to warn you that the formula is not the same as most of the formulas above and below. It is Excel’s job to highlight the problem. It is your discretion to decide whether it is a mistake or an intentional modification.
If it is a mistake, choose Copy formula from Above. If you want it this way, which is the case in this example, choose Ignore error.
Now can these type of errors be handled in bulk? What do you think? If you have followed these series of articles, you don’t even have to check. You should be able to guess the answer. As you can see, there is human discretion required to decide whether it is intentional or a mistake. Therefore, this type of error has to be handled one at a time. No bulk correction possible.
Inconsistency within a Table
We have seen how a table solves the problem of Formula Omits Adjacent Cells in this article. Tables automatically update all references when the data grows or shrinks – thus eliminating errors.
Tables also have another useful feature. Let us take the same data and convert it to a table first. I will add the formula later. We start with the Amount column only and convert it to a Table (Insert – Table). The tax formula will go into the cell B3.
You can use column names in formulas within a table
Now if I type = and click inside A3, the formula does not show the cell A3, instead it shows [@Amount]. This is a special feature of tables. You don’t need to type this whole thing. It appears automatically.
(Office 2007 onwards only. In 2007 the syntax is more lengthy. However, even the lengthy syntax is automatically added)
Now as soon as I type the formula and press ENTER…
Table copies formulas automatically
… Excel does many things automatically.
- It assumes that you want this to be added as a new calculated column
- It adds a new column as a part of the existing Table
- Inside a table, every column should have a name. A default name Column1 is added
- The formula is automatically copied all the way to the last row of the table
- This will work even if there were empty cells in the Amount column.
Changing the formula is also automatic
Now if you change the formula in any cell in the calculated column, it will automatically be copied to all the other cells in that column. In short, this has become a sort of auto-copy column.
This is a very good thing in most cases. However in case you want to change the formula for a particular cell, it is not possible. For example I want to change the formula in B7 to 10% tax, it will change it for all the rows. If this was not a table, the formula would change only for B7.
From this point of view, Table Auto-fill formula sounds like a nuisance in cases where you genuinely need exceptions.
But it is not so. Notice that although the formula changed to 10% for the entire column, a small little icon is trying to attract your attention. If you click on it – you will be surprised to know that this guy is aware of the fact that you may want this as an exception only for cell B7. Therefore, it is offering to undo the auto-copy.
Now only B7 has 10% tax and all other rows continue to have 2.5% tax. Nice – is it not? Of course, now the auto-fill will not work. You lose something to gain something.
However, the Excel error police detects this as an abnormal situation and therefore adds a green mark in cell B7. Clicking on the icon you will see that this is another type of error called Inconsistent Calculated Column Formula. The options are similar to the earlier error we saw above.
Actions are similar. In this case, you will want to Ignore error because this is a genuine requirement.
In case this was a mistake, you could always restore the automatic calculation by using the first option.
Now if you have many such exceptions, will you be able to handle this error globally? What do you think? In this case the answer is YES. Because Table is designed to have formula Auto-fill.
This is bad data
If you need too many exceptions, you will realize that ideally you should not hard code the amount of tax for each exception cell. Ideally you should have one more column with tax and then calculate it in a separate column. That makes it good data. Learn the principles of good data creation in this article: Good Data vs. Bad Data: How to identify?
Recap
Till now we have handled these types of errors. Remaining errors we will cover in the next (and last) article in this series about error checking.
***
