Rounding to match invoice

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
365, 2016
Platform
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,
 

Some videos you may like

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
Joined
Apr 18, 2011
Messages
35,915
Office Version
2010
Platform
Windows
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
Joined
Oct 24, 2015
Messages
5,319
round the result not a sum of rounded values

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,675
Office Version
365
Platform
Windows
How about
=ROUND(SUM(A1:A19),2)
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
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
Joined
Jan 14, 2003
Messages
1,791
Office Version
365, 2016
Platform
Windows
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>
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
365, 2016
Platform
Windows
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
Joined
Jan 14, 2003
Messages
1,791
Office Version
365, 2016
Platform
Windows
I need to copy and paste each row. after rounding.
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
365, 2016
Platform
Windows
Rick, I want it to be 320.04, but the sum of rounding adds up to 320.05.
 
Last edited:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
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?!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,087
Messages
5,466,550
Members
406,489
Latest member
Ankusharma

This Week's Hot Topics

Top