For business users of Microsoft Excel Free guides and templates
Home >  Excel Reports > 

Error-Reduction

How to Foot and Cross-Foot Excel Reports in a Floating-Point World

To reduce errors in Excel reports, you should foot and cross-foot them. But Excel's floating-point arithmetic gets in the way. Here's how to get around this problem.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
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 cross-foots the results.

An Excel Table that uses SUMIFS to summarize the data by region and product.

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 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:

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 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

An Excel table that's footed and cross-footed correctly.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),
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

An Excel table that's footed and cross-footed correctly.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. 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 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.

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.

 

Free Excel Dashboards

Click to see customer testimonials

Charley's SwipeFile charts