Rounding to match invoice

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,831
Office Version
  1. 365
Platform
  1. Windows
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.8199555

10.82
18.4957088

18.50
9.5174781

9.52
8.47837347

8.48
29.9121008

29.91
33.7986749

33.80
29.9121008

29.91
10.4897502

10.49
30.3520424

30.35
8.51195562

8.51
29.9121008

29.91
8.36794692

8.37
30.3520424

30.35
0.52941586

0.53
9.90074355

9.90
8.28971675

8.29
30.0682875

30.07
2.81809957

2.82
9.5174781

9.52
320.043972

320.05

<tbody>
</tbody>

Regards,
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.
 
Upvote 0
round the result not a sum of rounded values

A
B
C
24
320.043972​
320.04​
=ROUND(A24,2)
 
Upvote 0
How about
=ROUND(SUM(A1:A19),2)
 
Upvote 0
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:
Upvote 0
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.620.0203528
5505.520.0050882
0.0050882
2400.680.0159915
0.0159915
3984.040.0159915
0.0159915
2516.950.0162267
0.0162267
2402.780.0159915
0.0159915
2386.190.0162267
0.0162267
1970.520.0052931
0.0052931
2386.190.016075
0.016075
2424.350.0050882
0.0050882

<colgroup><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0
I need to copy and paste each row. after rounding.
 
Upvote 0
Rick, I want it to be 320.04, but the sum of rounding adds up to 320.05.
 
Last edited:
Upvote 0
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?!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top