# Rounding to match invoice

#### srizki

##### Well-known Member
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?

 10.82 10.82 18.4957 18.5 9.51748 9.52 8.47837 8.48 29.9121 29.91 33.7987 33.8 29.9121 29.91 10.4898 10.49 30.352 30.35 8.51196 8.51 29.9121 29.91 8.36795 8.37 30.352 30.35 0.529416 0.53 9.90074 9.9 8.28972 8.29 30.0683 30.07 2.8181 2.82 9.51748 9.52 320.044 320.05

<tbody>
</tbody>

Regards,

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### Rick Rothstein

##### MrExcel MVP
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

##### Well-known Member
round the result not a sum of rounded values

A
B
C
24
320.043972​
320.04​
=ROUND(A24,2)

#### Fluff

##### MrExcel MVP, Moderator
=ROUND(SUM(A1:A19),2)

#### joeu2004

##### Well-known Member
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)

Last edited:

#### srizki

##### Well-known Member
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.
 TOTAL OUR INT 531.62 0.0203528 5505.52 0.0050882 0.0050882 2400.68 0.0159915 0.0159915 3984.04 0.0159915 0.0159915 2516.95 0.0162267 0.0162267 2402.78 0.0159915 0.0159915 2386.19 0.0162267 0.0162267 1970.52 0.0052931 0.0052931 2386.19 0.016075 0.016075 2424.35 0.0050882 0.0050882

<colgroup><col><col></colgroup><tbody>
</tbody>

#### srizki

##### Well-known Member
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

##### Well-known Member
I need to copy and paste each row. after rounding.

#### srizki

##### Well-known Member
Rick, I want it to be 320.04, but the sum of rounding adds up to 320.05.

Last edited:

#### joeu2004

##### Well-known Member
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?!