Accountants like me do a lot of crossfooting. Crossfooting is basically summing the rows, summing the columns and making sure they match. A crossfooting example
Cell F6 is the crossfoot. In Excel, you can only have one formula in a cell, that is, you are either summing the row totals or the column totals, but not both.
Crossfooting presents two problems: If a formula is altered somewhere upstream of the crossfoot, the results will be in accruate. The total may be wrong for either the columns, the rows, or both. The other problem is decimal precision. Because Excel, like all applications, uses binary math instead of decimal math, the results can be ever so slightly different than if you did it by hand.
The formula in F6 is designed to alert you to these errors. It subtracts the sum of the row totals from the sum of the column totals. If everything is kosher, that should be zero. If an upstream formula gets screwed up, it most certainly won’t be zero and the formula will return “Error”.
But it doesn’t just compare it to zero. If it did, those pesky decimal-binary precision errors would show an error when there really isn’t one. So it takes the absolute value of that subtraction and compares it to .001 (the level of accuracy I want for this table). If the subtraction result is less than a tenth of a penny, I don’t care, so no error is displayed (The guys from Office Space might care, but I don’t).
That’s a great way to check totals. It would be very handy to check things like tolerances too.
I am attending a Accounting class and was having a heck of a time getting my cross footing to match.You gave me a clear concise answer to my problem thanks.
When entering this formula in Excel 2010, it did not allow me to use the <0.001 as a test.
Excel changed it to simply 0
This formula also does not give me the sum, but rather gives me a 0 in the cell.
Sorry, I had an error in the formula. It gives me the correct total, but still does not accept the 0.001 test value
What do you mean “it did not allow me to use”? Paste your formula here so we can see it.
What if you’re a using pivot table to do your summary? Do you still do a crossfooting check of some sort or simply trust the table results?
I’ve never seen an error with a pivot table, so I trust it.