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 this:
F3: =SUMIFS(Table[Value],Table[Region],$E3,Table[Product],F$2)
…where SUMIFS uses this syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, …, …)
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 these cells, which have the formulas shown:
H10: =H9-H7
H11: =H9=H7
That is, cell H10 shows that there’s a tiny difference between the values in the two cells, and that’s why 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 ten decimal places, I see that the actual number in the cell is: 3,218,623.4600000100
Keep in mind that all source data was rounded to the nearest penny.
This number illustrates that 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),
SUM(F3:F6), “Error”)
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
To foot and cross-foot in this example, we must perform three calculations.
Specifically, we need to compare the report’s column totals, its row totals, and the grand total of the Table’s Value column.
One way to do this is to use a long formula 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 rounded sum of the Value column equals the rounded sum of the total of the Total column. Next, we test whether the rounded sums of the Total row equals the total of the Total column. 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”)
That is, if the STDEV.S of any number of values is less that .00001, all the values are essentially equal in a floating point world.
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…
0.0000000316
…to…
0.0047140284
…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.
I admit, it does seem strange to use a standard deviation to test for equality in a floating point world. But it probably shouldn’t. The standard deviation calculation is, after all, a summary statistic.
