Rounding to match invoice

[srizki]

Hi all,
Every month I do some manual calculations, in which I have to manually round number to two decimals to match the invoice amount, here is an example, the second column rounded to 2 decimals, =round(…,2), that comes up to 320.05 but the actual should be 320.04, therefore I manually adjust the amounts to match the invoice amount.

Is there an easy way of doing this?

[TABLE="width: 0"]
<tbody>[TR]
[TD]10.8199555
[/TD]
[TD]
[/TD]
[TD]10.82
[/TD]
[/TR]
[TR]
[TD]18.4957088
[/TD]
[TD]
[/TD]
[TD]18.50
[/TD]
[/TR]
[TR]
[TD]9.5174781
[/TD]
[TD]
[/TD]
[TD]9.52
[/TD]
[/TR]
[TR]
[TD]8.47837347
[/TD]
[TD]
[/TD]
[TD]8.48
[/TD]
[/TR]
[TR]
[TD]29.9121008
[/TD]
[TD]
[/TD]
[TD]29.91
[/TD]
[/TR]
[TR]
[TD]33.7986749
[/TD]
[TD]
[/TD]
[TD]33.80
[/TD]
[/TR]
[TR]
[TD]29.9121008
[/TD]
[TD]
[/TD]
[TD]29.91
[/TD]
[/TR]
[TR]
[TD]10.4897502
[/TD]
[TD]
[/TD]
[TD]10.49
[/TD]
[/TR]
[TR]
[TD]30.3520424
[/TD]
[TD]
[/TD]
[TD]30.35
[/TD]
[/TR]
[TR]
[TD]8.51195562
[/TD]
[TD]
[/TD]
[TD]8.51
[/TD]
[/TR]
[TR]
[TD]29.9121008
[/TD]
[TD]
[/TD]
[TD]29.91
[/TD]
[/TR]
[TR]
[TD]8.36794692
[/TD]
[TD]
[/TD]
[TD]8.37
[/TD]
[/TR]
[TR]
[TD]30.3520424
[/TD]
[TD]
[/TD]
[TD]30.35
[/TD]
[/TR]
[TR]
[TD]0.52941586
[/TD]
[TD]
[/TD]
[TD]0.53
[/TD]
[/TR]
[TR]
[TD]9.90074355
[/TD]
[TD]
[/TD]
[TD]9.90
[/TD]
[/TR]
[TR]
[TD]8.28971675
[/TD]
[TD]
[/TD]
[TD]8.29
[/TD]
[/TR]
[TR]
[TD]30.0682875
[/TD]
[TD]
[/TD]
[TD]30.07
[/TD]
[/TR]
[TR]
[TD]2.81809957
[/TD]
[TD]
[/TD]
[TD]2.82
[/TD]
[/TR]
[TR]
[TD]9.5174781
[/TD]
[TD]
[/TD]
[TD]9.52
[/TD]
[/TR]
[TR]
[TD]320.043972
[/TD]
[TD]
[/TD]
[TD]320.05
[/TD]
[/TR]
</tbody>[/TABLE]

Regards,

 

[srizki]

The question is that when I sum column 1 below, the total comes to 320.4 and that is correct, but when I round it using round function, the total is 320.05 that is not the amount in the invoice, and you can see the individual amounts are same in the two columns, but the total is different.
[TABLE="width: 252"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]2 decimals[/TD]
[TD] Rounded[/TD]
[/TR]
[TR]
[TD="align: right"]10.8199555[/TD]
[TD="align: right"]10.82[/TD]
[TD="align: right"]10.82[/TD]
[/TR]
[TR]
[TD="align: right"]18.4957088[/TD]
[TD="align: right"]18.50[/TD]
[TD="align: right"]18.50[/TD]
[/TR]
[TR]
[TD="align: right"]9.5174781[/TD]
[TD="align: right"]9.52[/TD]
[TD="align: right"]9.52[/TD]
[/TR]
[TR]
[TD="align: right"]8.47837347[/TD]
[TD="align: right"]8.48[/TD]
[TD="align: right"]8.48[/TD]
[/TR]
[TR]
[TD="align: right"]29.9121008[/TD]
[TD="align: right"]29.91[/TD]
[TD="align: right"]29.91[/TD]
[/TR]
[TR]
[TD="align: right"]33.7986749[/TD]
[TD="align: right"]33.80[/TD]
[TD="align: right"]33.80[/TD]
[/TR]
[TR]
[TD="align: right"]29.9121008[/TD]
[TD="align: right"]29.91[/TD]
[TD="align: right"]29.91[/TD]
[/TR]
[TR]
[TD="align: right"]10.4897502[/TD]
[TD="align: right"]10.49[/TD]
[TD="align: right"]10.49[/TD]
[/TR]
[TR]
[TD="align: right"]30.3520424[/TD]
[TD="align: right"]30.35[/TD]
[TD="align: right"]30.35[/TD]
[/TR]
[TR]
[TD="align: right"]8.51195562[/TD]
[TD="align: right"]8.51[/TD]
[TD="align: right"]8.51[/TD]
[/TR]
[TR]
[TD="align: right"]29.9121008[/TD]
[TD="align: right"]29.91[/TD]
[TD="align: right"]29.91[/TD]
[/TR]
[TR]
[TD="align: right"]8.36794692[/TD]
[TD="align: right"]8.37[/TD]
[TD="align: right"]8.37[/TD]
[/TR]
[TR]
[TD="align: right"]30.3520424[/TD]
[TD="align: right"]30.35[/TD]
[TD="align: right"]30.35[/TD]
[/TR]
[TR]
[TD="align: right"]0.52941586[/TD]
[TD="align: right"]0.53[/TD]
[TD="align: right"]0.53[/TD]
[/TR]
[TR]
[TD="align: right"]9.90074355[/TD]
[TD="align: right"]9.90[/TD]
[TD="align: right"]9.90[/TD]
[/TR]
[TR]
[TD="align: right"]8.28971675[/TD]
[TD="align: right"]8.29[/TD]
[TD="align: right"]8.29[/TD]
[/TR]
[TR]
[TD="align: right"]30.0682875[/TD]
[TD="align: right"]30.07[/TD]
[TD="align: right"]30.07[/TD]
[/TR]
[TR]
[TD="align: right"]2.81809957[/TD]
[TD="align: right"]2.82[/TD]
[TD="align: right"]2.82[/TD]
[/TR]
[TR]
[TD="align: right"]9.5174781[/TD]
[TD="align: right"]9.52[/TD]
[TD="align: right"]9.52[/TD]
[/TR]
[TR]
[TD="align: right"]320.043972[/TD]
[TD="align: right"]320.04[/TD]
[TD="align: right"]320.05[/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

The question is that when I sum column 1 below, the total comes to 320.4 [sic; 320.04] and that is correct, but when I round it using round function, the total is 320.05 that is not the amount in the invoice

Nothing can be done about that. It is a numerical fact that the rounded sum of the parts might not be the same as the sum of the rounded parts. That is why most financial statements have a footnote to the effect that "numbers may not add up due to rounding" [1].

For a customer invoice, it is a "best practice" to sum the rounded line items. So 320.05 is following "best practice" guidelines.


-----
[1] https://www.investopedia.com/terms/r/rounding-error.asp

 

[srizki]

Thanks

 

[joeu2004]

Nothing can be done about that.

I mean: if you simply sum the rounded amounts.

A dubious alternative is to modify one or more rounded amounts so that their total is the same as rounded sum of the unrounded amounts.

For example, and arguably a bad approach: round all but the last amount as usual. For the last amount, use a formula of the form:

=ROUND(SUM(A1:A19),2) - SUM(B1:B18)

To demonstrate the flaw of that approach, enter 10.005 into A1:A19, and enter =ROUND(SUM(A1:A19),2) into A20. Enter =ROUND(A1,2) into B1 and copy B1 into B2:B18. In B19, enter =A20-SUM(B1:B18). The value in B19 is 9.92, significantly different from 10.005.

(Some people mistakenly think that using so-called banker's rounding will avoid such major differences. Obviously not for this example.)

There are alternatives that minimize the error in the last amount by distributing the error among all of the amounts. Either way, these approaches result in misstating one or more individual amounts.

 

[srizki]

Thanks Joeu,

Yes, I have done that, I have tried everything, but as you said, there is a flaw of this approach. I just adjust the number somewhere, where it is least effective.
thanks again for your time.