identify rounding error

Question

In the attached example, the value in column A is supposed to add up

to the sum of B and C.  Because of rounding error in the calculation 

of B and C, the total in column A may be off by .01. The penny difference 

is not important per se, but can be visually distracting when the 

figures are shown in a report. And if the difference is greater than a

.01, it could be caused by an error in the analysis routine.

Column D is obtained by manually adding together the numbers in

columns B and C. Column E compares column D to column A.

Is there a way, perhaps with a user-defined function, to automate the

comparison process?

Office 365. Excel 2013.

Answer 1

I wrote:

That's fine, as I had already said.  But I can tell you that selectively choosing ROUNDUP/DOWN, even dynamically using VBA, is not a good approach.  The judicious use of ROUND should lead to consistent results. 

Here is the example that you provide, with corrections.

The original formula in G2 was =50%*F2.  As I speculated before, that results in 9.655, which is displayed as 9.66 only because of the cell format.  Formatting alone to display 2 decimal places does not change the underlying value.

(Unless the option "Precision as displayed" is set, which I strongly discourage.)

Instead, the formula should be =ROUND(50%*F2,2), since you expect it to be accurate to the cent.

The orignal formula in H2 was the same as G2, with the same unintended consequences.  But simply rounding would result in a penny-off error sometimes.

Instead, the formula in H2 should be =F2-G2.  And it is more reliable to write =ROUND(F2-G2,2), for very different reasons that I will explain below.

Finally, the formulas in G6 and H6 were =SUM(amount), where amount was the named range =MAIN!$F$2:$F$5.  Of course, that produces the wrong totals for column G and H.

Change the formula in F6 to =SUM(F2:F5), and copy across into G6:H6.  Or change the named range to =MAIN!F$2:F$5, changing $F to F.  And again, is it is more reliable to write =ROUND(SUM(...),2).

However, P might think it is unfair that he/she is always paying more of the total; the difference will depend on the number of odd-priced items (3 in your example).

IMHO, the simplest remedy is to add an Adjustment line item at the end.  Something like this:

Formulas:

G6: =ROUND(IF(ISODD(MONTH(TODAY())),ROUNDDOWN(50%*F7,2),ROUNDUP(50%*F7,2))-SUM(G2:G5),2)

H6: =-G6

F7: =ROUND(SUM(F2:F6),2)

Copy F7 into G7:H7

I would format G6:H6 as Custom +0.00;-0.00;"" .  Thus, zero appears blank.  If you prefer, replace "" with 0.00 .

The formula in G6 is intended to alternate the months that P or J might pay a penny more.  But as luck would have, P might pay a penny more every even month, but neither pays more every odd month.  So P might pay as much as 6 cents more for the year.

My response to that is:  "Here's a dime, P.  Now, get outta my face!" ;-)

[EDIT] And in fact, I might simplify G6 to =ROUND(ROUND(50%*F7,2)-SUM(G2:G5),2) in the first place, and give P $0.15 if he/she complains about paying as much as $0.12 more for the year.  Meh!

---

Explaining =ROUND(F2-G2,2) et al....

In general, whenever you expect arithmetic with non-integers to be accurate to n decimal places (n=2 in this case), it is prudect to explicitly round the arithmetic to that number of decimal places.  To reiterate:  that number, not an arbitrary number like 10 decimal places, as some people suggest.

The reason is:  Excel represents values in binary.  Most decimal fractions cannot be represented exactly in binary, resulting in infinitesimal differences that we usually cannot see because Excel displays only the first 15 significant digits.

The infinitesimal differences can accumulate to the point where they might make a difference, especially when comparing values.  For example, IF(10.12 - 10 = 0.12, TRUE) returns FALSE(!).  In this example, we can see that 10.12 - 10 is about 0.119999999999999, not 0.12.  IF(ROUND(10.12 - 10,2) = 0.12, TRUE) returns TRUE as expected.

Answer 2

cellist.usa wrote:

In the attached example, the value in column A is supposed to add up to the sum of B and C.  Because of rounding error in the calculation of B and C, the total in column A may be off by .01. [....] Column D is obtained by manually adding together the numbers in columns B and C. Column E compares column D to column A. Is there a way, perhaps with a user-defined function, to automate the comparison process?

There are many ways that might remedy the problem.  You probably don't need a UDF.  But you do not provide sufficient information for us to offer a dispositive answer.

Instead of posting an image, it would be better if you upload an example Excel file (redacted) that demonstrates the problem to a file-sharing website, and post the public/share URL in a response here.

You are probably being fooled by the cell formats.  If you format columns A, B and C to display more decimal places, you will probably see that 57.73, 25.57 and 32.15 are more like 57.728, 25.574 and 32.154.

The first question is:  are the values in column B and C constants or formulas?

If the latter, perhaps you want to change the formulas to explicitly round to 2 decimal places, thus:  =ROUND(expression,2).  Alternatively, if column A has formulas of the form =B4+C4, perhaps you want to change it to =ROUND(B4,2)+ROUND(C4,2).

The choice depends on your goals.  So only you can decide what is best.

Answer 3

PS....

cellist.usa wrote:

The penny difference [...] can be visually distracting when the figures are shown in a report. And if the difference is greater than a .01, it could be caused by an error in the analysis routine.

Such penny-off-and-more differences are not uncommon in reports.  Most pro forma financial reports have a footnote stating that calculated numbers might not match numbers shown due to rounding differences.  But I understand the desire to avoid the differences, when feasible.

Answer 4

@joeu

"Instead of posting an image, it would be better if you upload an example Excel file (redacted) that demonstrates the problem to a file-sharing website, and post the public/share URL in a response here."

I have prepared a version of the spreadsheet that illustrates the situation and redacts personal info in the data itself. Problem is, I need to conceal the personal information stored in the account section. How can I do that?

"If the latter, perhaps you want to change the formulas to explicitly round to 2 decimal places, "

I'll give that a try also. But please let me know how to conceal the account info so I can upload an actual copy of the workbook.

Thanks.

Answer 5

cellist.usa wrote:

"If the latter, perhaps you want to change the formulas to explicitly round to 2 decimal places, "

I'll give that a try also. But please let me know how to conceal the account info so I can upload an actual copy of the workbook.

I understand the desire for anonymity. Not sure what you mean by "account info", and I'm not familiar with Office 365 Excel (2016).

In my Excel version, I go to Excel Options > Popular and under "Personalize your copy", I change User Name.

If you are referring to account info of the file-sharing website that you chose, I set up special accounts for the express purpose of sharing files anonymously.

If you are still uncomfortable (understandably) and the explicit rounding suggestion does not remedy your problems, perhaps you would not mind sending the Excel file to just me at joeu2004 "at" hotmail.com.

But I do expect that will be unnecessary, once you look at the values with a sufficient number of decimal places.