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

Are we done here?  If rounding solves the problem, please mark one of my responses as "answer" so we know the problem is solved.

Answer 3

@JO

"Are we done here? "

You've given me a good idea and I'm working on it. I will come back and report my results and will mark the question answered if I'm able to fix the problem. 

Thanks.

Answer 4

@JO

"Are we done here?"

Working with ROUND, I happened onto ROUNDUP and ROUNDDOWN.

ROUND didn't seem to help but, using ROUNDUP and ROUNDDOWN, 

I was able to automate identifying transactions where a rounding 

adjustment was needed. I wasn't able to automate picking up the

adjusted amounts but maybe that could be done with VBA code.

I've created a workbook that provides an example of this and I can upload it

to ONEDRIVE. I understand now that there's no risk of anyone 

being able to see my account information, but I'd still prefer to send

the link to you privately. 

Let me know.

Answer 5

cellist.usa wrote:

I've created a workbook that provides an example of this and I can upload it to ONEDRIVE. [....] I'd still prefer to send the link to you privately. Let me know.

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.  So I hope your examples are complete enough that I can see the entire derivation of values, not just selective isolated calculations that you think (perhaps incorrectly) are sufficient to demonstrate when you need to ROUNDUP/DOWN.  There is an alternative to explicit rounding.  I usually discourage it; but your situation might be one of the few exceptions.  I don't want to get into without seeing your examples first.