Rounding millions for financial presetations that must foot.

erockthenight

New Member
Joined
Aug 5, 2013
Messages
1
Greetings. I have rounding issue. I'm working with whole numbers, and rounding into millions. For example, the whole numbers are (bold is total):

(2,339,425)
8,260,205
3,222,786
9,143,566


<colgroup><col></colgroup><tbody>
</tbody>
Rounded (using the ROUND function):
(2.3)
8.3
3.2
9.1

My problem is when you add up (with a calculator outside Excel) the rounded numbers, you will arrive at 9.2. The audit committee chairman will add these rows of rounded numbers and they must foot, or the CFO will have egg on his face. Any suggestions? I'm beyond over manually adjusting the rounded numbers up or down to solve this summation problem as the underlying slide is linked to the supporting Excel models.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Any CFO knows that rounded numbers will not foot for exactly that reason. Fudging one of the addends (or the sum) to make it foot is a much worse sin, IMO.
 
Last edited:
Upvote 0
Welcome to MrExcel.

This is how I do it (copy the formula in B2 down):

Excel 2010
A
B
1
2
-2339425
-2.3
3
8260205
8.2
4
3222786
3.2
5
9143566
9.2

<TBODY>
</TBODY>
Sheet4

Worksheet Formulas
Cell
Formula
B2
=ROUND(SUM(A$2:A2)/1000000,1)-SUM(B$1:B1)

<TBODY>
</TBODY>


<TBODY>
</TBODY>
 
Upvote 0
As per shg's post, can you perhaps just add a footnote saying something like:

"Numbers are displayed rounded to one decimal place. Totals are calculated based on the original (not rounded) figures."

Or alternatively, show the total based on the sum of the rounded amounts and make it clear in the footnote that is what has been done.
 
Upvote 0
Excel Workbook
ABCD
1
2-2339425-2.3-2.3-2.3
382602058.38.28.2
432227863.23.23.2
591435669.29.19.1
1e
Excel 2003
Cell Formulas
RangeFormula
B2=ROUND(A2/1000000,1)
B5=SUMPRODUCT(ROUND(A2:A4/1000000,1))
C2=IF(B2=MAX($B$2:$B$4),B2+($C$5-SUM($B$2:$B$4)),B2)
C5=ROUND(A5/1000000,1)
D2=ROUND(SUM(A$2:A2)/1000000,1)-SUM(C$1:C1)
D3=ROUND(SUM(A$2:A3)/1000000,1)-SUM(C$1:C2)
D4=ROUND(SUM(A$2:A4)/1000000,1)-SUM(C$1:C3)
D5=ROUND(A5/1000000,1)


Andrew's solution is neater; see Column D.

If the information will show correctly with 9.2, see B5

=SUMPRODUCT(ROUND(A2:A4/1000000,1))

If the information must show the rounded total of 9.1 and you can use
column B as a helper column, see column C.
 
Last edited:
Upvote 0
Excel Workbook
ABCD
1
2-2339425-2.3-2.3-2.3
382602058.38.28.2
432227863.23.23.2
591435669.29.19.1
1e
Excel 2003
Cell Formulas
RangeFormula
B2=ROUND(A2/1000000,1)
B5=SUMPRODUCT(ROUND(A2:A4/1000000,1))
C2=IF(B2=MAX($B$2:$B$4),B2+($C$5-SUM($B$2:$B$4)),B2)
C5=ROUND(A5/1000000,1)
D2=ROUND(SUM(A$2:A2)/1000000,1)-SUM(D$1:D1)
D5=ROUND(A5/1000000,1)


Please see corrected information above.
I was making changes and I did not make the last change in time.

Andrew's solution is neater; see Column D.

If the information will show correctly with 9.2, see B5

=SUMPRODUCT(ROUND(A2:A4/1000000,1))

Dave
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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