When reviewing an Excel spreadsheet it is important to understand the typical errors people make. Below are ideas on easy ways of finding the SUM errors, in order of riskiness, when auditing an Excel Spreadsheet.
It is useful to have an spreadsheet error add in to find all the risky formulas but if you don’t have one you can do a manual search using the FIND tool.
SUM ignores cells that it does not see as a number
The SUM function acts similar to AVERAGE, MAX and MIN in that it
- ignores blanks (not an issue here as blank and zero would give the same result),
- ignores anything that doesn’t look like a number which could be word, or it could be a number formatted as text or a number that has a strange space in.
Below a listing of the SUM functions from the Formula Report.
If you go and look at the cell, C27 has the individual calculation (=C9+C10+..) and it is giving a VALUE error. The SUM in C25 is giving a number but it is horribly wrong as just the first cell in the range is bigger than it. The SUM function is ignoring any number bigger than 3 characters due to a unique space type that Excel sometimes sees. So when you see a SUM, remember to be concerned if you find any numbers that are coming into Excel in a strange format or else treated by Excel as text.
References to a long list of individual cells
The other risk is when there is a long list of cells that, presumably, a user has clicked on individually either within a SUM or with +’s. The risk here is simply pointing to the wrong cell.
So, below, although this looks like a simple formula, it is generally quick to check and it will either immediately look right or wrong.
In this case you will see that the cell D9 has been missed as the person clicked on D8 instead.
Summing through sheets or 3D formula
There is a way to use a SUM formula to sum through sheets, sometimes referred to as 3D formula. Effectively it means that Excel will add the same cell from every sheet BETWEEN a start and end sheet. The risk here is that a user does not understand how the formula works and moves the sheets around for some other reason (easier to compare 2 sheets if next to each other) but this changes the results.
As shown below, if you have a sum which seems to only refer to a single cell (G7 below) but has 2 sheet names, it is a 3D formula. In this case the order of the sheets becomes important so either they need to be protected or all users must be aware of what happens when you move sheets around.
