Rounding in Thousands

  • Thread starter Thread starter TKI
  • Start date Start date
T

TKI

I run a P&L report in Excel where the revenue and expense
lines are displayed in thousands, not the actual dollar
amount. Because they are displayed in thousands, the sum
of each line does not always tie to the total line (for
example the sum of the expense lines doesn't equal the
total expense line). I was told that I now need to
manipulate the report so that the sum of the values of
each line foot to the bottom line. Is there a formula
that I can use that will allow me to accomplish this in
some automated way?
Thanks.
 
I agree with Frank, but be aware that simply using ROUND() may not solve
your problem - there can still be discrepancies in Totals, especially if
you're dividing one number by another.

See

http://www.mcgimpsey.com/excel/pennyoff.html

The only defense is to design your spreadsheet to specifically account
for rounding error.
 
My problem is that if I round each line item, my total
line will be off from the real total by at least $1K

For Example:

Real Dollar Rounded
Salaries 945,320 945
Bonus 235,964 236
Fringe Benefits 278,087 278
Temp Help 47,380 47

TOTAL 1,506,751 1,506

Notice how the total would be more accurate as $1,507 but
when I sum the rounded numbers the total comes out to
$1,506.
So my questions is, is there a relatively simple way to
manipulate the line detail so that the displayed values
will total $1,507 when rounded in thousands? (for example
changing the Salaries amount from 945 to 944). I was told
to make corrections to the line detail instead of the
total line so that the displayed total will be accurate.
The Total line is a simple sum formula.
 
The simplest way I can think of is to calculate the total, but display
rounded. For instance, in the example you give:

A B C
1 Real Dollar Rounded
2 Salaries 945,320 =C6-SUM(C3:C5)
3 Bonus 235,964 =ROUND(B3, -3)
4 Bonus 278,087 =ROUND(B4, -3)
5 Temp Help 47,380 =ROUND(B5, -3)
6 TOTAL 1,506,751 =ROUND(B6, -3)

You can then hide Column B and format column C with something like

Format/Cells/Number/Custom 0,_);(0,);0_);@
 
Back
Top