ErrorReduction
How to Foot and CrossFoot Excel Reports in a FloatingPoint World
To reduce errors in Excel reports, you should foot and
crossfoot them. But Excel's floatingpoint arithmetic gets in
the way. Here's how to get around this problem.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

At first glance, the following report is an ordinary one. It
merely sums an Excel Table by Product and Region, and then foots
and crossfoots the results.
The body of the report uses formulas like the one for the
cell shown...
F3:
=SUMIFS(Table[Value],Table[Region],$E3,Table[Product],F$2)
...where
SUMIFS uses this syntax:
=SUMIFS(sum_range, criteria_range1, criteria1,
criteria_range2, criteria2, ...)
A wellconstructed 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:
H10: =H9H7
H11: =H9=H7
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
Floatingpoint 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 crossfoot correctly.
How to Foot and CrossFoot Reliably
If
we didn't have problems with floatingpoint 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 floatingpoint 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.
Crossfooting 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
floatingpoint 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.
Specificlaly,
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
nearlyzero 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.
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.
