Rounding millions for financial presetations that must foot.

[erockthenight]

Greetings. I have rounding issue. I'm working with whole numbers, and rounding into millions. For example, the whole numbers are (bold is total):

[TABLE="width: 95"]
<colgroup><col></colgroup><tbody>[TR]
[TD](2,339,425)[/TD]
[/TR]
[TR]
[TD]8,260,205
[/TD]
[/TR]
[TR]
[TD]3,222,786
[/TD]
[/TR]
[TR]
[TD]9,143,566

[/TD]
[/TR]
</tbody>[/TABLE]
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.

 

[shg]

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.

 

[Andrew Poulsom]

Welcome to MrExcel.

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

Excel 2010

	A	B

<TBODY>
[TD="align: center"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]-2339425
[/TD]
[TD="align: right"]-2.3
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]8260205
[/TD]
[TD="align: right"]8.2
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]3222786
[/TD]
[TD="align: right"]3.2
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"]9143566
[/TD]
[TD="align: right"]9.2
[/TD]

</TBODY>

Sheet4​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<TBODY>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]B2
[/TH]
[TD="align: left"]=ROUND(SUM(A$2:A2)/1000000,1)-SUM(B$1:B1)
[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
</TBODY>[/TABLE]

 

[circledchicken]

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.

 

[Dave Patton]

Excel Workbook
	A	B	C	D
1
2	-2339425	-2.3	-2.3	-2.3
3	8260205	8.3	8.2	8.2
4	3222786	3.2	3.2	3.2
5	9143566	9.2	9.1	9.1
1e

Excel 2003

Cell Formulas
Range	Formula
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.

 

[Dave Patton]

Excel Workbook
	A	B	C	D
1
2	-2339425	-2.3	-2.3	-2.3
3	8260205	8.3	8.2	8.2
4	3222786	3.2	3.2	3.2
5	9143566	9.2	9.1	9.1
1e

Excel 2003

Cell Formulas
Range	Formula
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