Microsoft Excel: Fixing a proofing formula that fails every time

Q. In Excel, I’m calculating the difference between times in hours, but the results I’m getting don’t exactly agree with my proof calculation (calculated based on the difference between my total ending times less my total beginning times). Can you please look at my worksheet and tell me what I’m doing wrong?

A. The reader provided me with an example worksheet containing thousands of time difference calculations, which I verified as being correct. I also verified that the reader’s proofing formula was correct. Nonetheless, the reader’s proof calculation did not exactly agree with the time difference calculation totals, so an error message was triggered. What was going on? The problem, it turns out, is related to Excel’s famous built-in 15-digit number limitation, which prevented the data totals from exactly tying to the proofing total. As explained in my March 2014 Technology Q&A item “Bugged by Excel’s Calculation Errors,” Excel is designed to handle only 15 total digits for a given number (including digits after the decimal place, when applicable). For numbers containing more than 15 digits, Excel rounds the digits after the 15th place down to zero.

To illustrate the reader’s problem, consider the following simplified example pictured below. The time data shown in cells A3 and B3 are exactly 10 days and 10 hours apart. In cell C3, I’ve entered a simple subtraction formula that calculates the difference between these two times to be 10.41666667, which equates to 10 days and 41.666667% of an additional day. You can prove this works by multiplying 10.41666667 times 24 (for 24 hours in a day) to derive 250 total hours, as shown in cell C5.

So far, everything works fine; however, the problem emerges when we multiply 10.41666667 by 24, and then display the results using 15-digit formatting, as has been done in cell C7. As you can see in cells C5 and C7 above, both cells contain the exact same formula but display different amounts because they are formatted differently. From this, we see that when formatted as an integer, cell C5 displays 250 when in fact the true number contained in that cell is 249.999999999942 as shown in cell C7 (when formatted as a 15-digit number). So, in this case the reader did everything right but failed to account for Excel’s built-in mathematical limitation. To avoid this problem, the reader should insert the ROUND function in his proofing formulas to make them work properly.

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a CPE instructor, and a JofA contributing editor.

Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.