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,

 

[Rick Rothstein]

Why do you think 320.043972 should round to 320.05? The part digit in the third decimal place (3) is less than 5, so I do not see why you want round up for it.

 

[sandy666]

round the result not a sum of rounded values

[Table="width:, class:head"][tr=bgcolor:#000000][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

24​

[/td][td]

320.043972​

[/td][td]

320.04​

[/td][td]=ROUND(A24,2)[/td][/tr]
[/table]

 

[Fluff]

How about
=ROUND(SUM(A1:A19),2)

 

[joeu2004]

the second column rounded to 2 decimals, =round(…,2), that comes up to 320.05 but the actual should be 320.04

Huh?! Don't you mean just the opposite? Your subject line says "rounding to match invoice". The invoice has rounded line items; presumably, the invoice should have the sum of the rounded line items. Ergo, I would want the sum of the unrounded amounts (320.04397204) to equal the sum of the rounded amounts (3202.05). To that end:

=SUMPRODUCT(ROUND(A1:A19),2)

 

[srizki]

Rick,
You are right, by rounding it never matches to invoice amount. In this case, the invoice amount is $320.04, while after all the calculations, separate overhead and operation expenses the results are in many decimals, so when round them it never matches to the invoice amount.
The invoice has a total amount, I multiply the amounts with our interest, each expense is separated into two parts, overhead and operations, that is why there are these discrepancies.
[TABLE="width: 221"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD] TOTAL[/TD]
[TD] OUR INT[/TD]
[/TR]
[TR]
[TD="align: right"]531.62[/TD]
[TD="align: right"]0.0203528[/TD]
[/TR]
[TR]
[TD="align: right"]5505.52[/TD]
[TD="align: right"]0.0050882[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.0050882[/TD]
[/TR]
[TR]
[TD="align: right"]2400.68[/TD]
[TD="align: right"]0.0159915[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.0159915[/TD]
[/TR]
[TR]
[TD="align: right"]3984.04[/TD]
[TD="align: right"]0.0159915[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.0159915[/TD]
[/TR]
[TR]
[TD="align: right"]2516.95[/TD]
[TD="align: right"]0.0162267[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.0162267[/TD]
[/TR]
[TR]
[TD="align: right"]2402.78[/TD]
[TD="align: right"]0.0159915[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.0159915[/TD]
[/TR]
[TR]
[TD="align: right"]2386.19[/TD]
[TD="align: right"]0.0162267[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.0162267[/TD]
[/TR]
[TR]
[TD="align: right"]1970.52[/TD]
[TD="align: right"]0.0052931[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.0052931[/TD]
[/TR]
[TR]
[TD="align: right"]2386.19[/TD]
[TD="align: right"]0.016075[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.016075[/TD]
[/TR]
[TR]
[TD="align: right"]2424.35[/TD]
[TD="align: right"]0.0050882[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.0050882[/TD]
[/TR]
</tbody>[/TABLE]

 

[srizki]

No, the first column is my calculated numbers that I round, second column, but the invoice amount is $320.04, I can not copy and paste the rounded amounts, that would not tie to the invoice, so I adjust her and there in my calculated column to match the invoice amount, just by rounding it does not match.

Thanks

 

[srizki]

I need to copy and paste each row. after rounding.

 

[srizki]

Rick, I want it to be 320.04, but the sum of rounding adds up to 320.05.

 

[joeu2004]

No, the first column is my calculated numbers that I round, second column, but the invoice amount is $320.04

So you want the total invoice amount (320.04) not to be the sum of the individual amounts shown on the invoice?! Makes no sense to me. Your estute customers will raise questions.

In any case, I do not understand the problem.

Certainly, you cannot sum the rounded amounts (column B) and expect to get anything other than 320.05. That is, if you have only column B, you cannot know what the unrounded values were.

So if you want 320.04, you must sum the unrounded amounts (column A). If you only need that sum to appear to be rounded, simply format the cell with 2 decimal places. If you want the actual sum to be rounded to 320.04, "Fluff" provided you with the formula.

And if you wanted the sum of the unrounded amounts to match the sum of the rounded amounts on the invoice (per your subject line), I provided you with the formula. But you keep insisting that is not what you want (!).

So what exactly are we still discussing?!