At first glance, the following report is an ordinary one. It merely sums an Excel Table by Product and Region, and then foots and cross-foots the results.
It’s all very ordinary. There’s nothing more to see here.
Or is there?
The body of the report uses formulas like the one for the cell shown…
…where SUMIFS uses this syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …)
A well-constructed version of this report would add tests to the row and column totals, tests that make sure that the report isn’t overlooking new Regions or Products that were added to the source data since the last time the report was generated.
The easiest way to perform such a test in cell H7 would be to find the grand total of column A in the Table and then return an error if the sums of the two sets of report totals aren’t equal.
If you visually compare the total in cell H7 with the Grand Total in cell H9, they appear to be equal. But look above at the conflicting results in the following two cells, which have the formulas shown:
That is, cell H10 shows that there’s a tiny difference between the values in the two cells, and cell H11 shows that the two totals aren’t equal from Excel’s perspective.
In Floating-point arithmetic may give inaccurate results in Excel, Microsoft discusses the reason for these problems. The article explains that because of the way computers convert decimal data to binary data, small inaccuracies necessarily will be included in decimal numbers.
To illustrate, when I format cell F3 to eight decimal places, I see that the actual number in the cell is: 3,218,623.46000001
Keep in mind that all source data was rounded to the nearest penny.
Therefore, when Excel sums many decimal numbers, you can’t rely on using an ordinary comparison to test whether the results foot and cross-foot correctly.
How to Foot and Cross-Foot Reliably
If we didn’t have problems with floating-point math, this formula would could test the results in cell F7:
F7: =IF(SUMIFS(Table[Value],Table[Product],F$2)= SUM(F3:F6), SUM(F3:F6), “Error”)
Here, we use SUMIFS to sum the Values for Hats, for all Regions. If that value equals the equivalent report total, we return whichever total is quick and easy to calculate. Otherwise, we return “Error.”
But unfortunately, the floating-point problem makes that test inaccurate. Therefore, we need to use the ROUND function to round the results of each summary calculation:
F7: =IF(ROUND(SUMIFS(Table[Value],Table[Product],F$2),2)= ROUND(SUM(F3:F6),2),
Here, by rounding all the results we’re able to use the equal sign. Cross-footing works the same way, of course.
One Way to Test the Grand Total Reliably
Testing the grand total always is a good idea. But here, in cell H7, we must test three values, not two.
That is, we need to compare the horizontal report total, the vertical report total, and the grand total of the table data.
We have two choices. The obvious choice is to use a test like this:
H7: =IF( (ROUND(SUM(Table[Value]),2)=ROUND(SUM(H3:H6),2))*
(ROUND(SUM(F7:G7),2)=ROUND(SUM(H3:H6),2)), SUM(H3:H6), “Error”)
Here, we first check whether the grand total equals one of the report totals. Next, we test that the rounded versions of the two report totals are equal. If both tests succeed, their product is TRUE, and we know that all three rounded numbers match. So we return a report total; otherwise, we return “Error.”
Another Way to Test the Grand Total
The problem with the previous test is that we only can test one pair of numbers at a time, which creates a long formula. So here’s another approach you might consider:
The standard deviation is a measure of how widely values are dispersed from their average value (the mean). Therefore, the standard deviation of several numbers that are equal— except for floating-point issues—should be nearly zero.
With this thought in mind, we can test that the three numbers are essentially equal by testing that their standard deviation is essentially zero. Specifically, this formula checks whether their standard deviation is less than .00001:
H7: =IF(STDEV.S(SUM(Table[Value]), SUM(H3:H6), SUM(F7:G7))<0.00001,SUM(H3:H6),”Error”)
I created this test only recently, so I haven’t used it much. But testing shows that if I add one penny to any of the row or column totals in the table above, the STDEV.S jumps from its nearly-zero value of…
…so using a cutoff value of .00001 seems to be a safe way to differentiate between results that are essentially equal and those that are not.
The really nice thing about testing for equality with the standard deviation is that you can use it to test any number of numbers with one easy formula.