Re: Rounding in Thousands

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: David McRitchie (dmcritchie_at_msn.com)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 15:35:31 -0400

Hi ...TKI...,

Formatting the cells as
      0,_);(0,);0_);@

will round to the thousands, without changing the value.

Additional Examples of Cell Formatting in
   http://www.mvps.org/dmcritchie/excel/formula.htm#getformat 

---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
"TKI" <anonymous@discussions.microsoft.com> wrote in message news:c3d801c48a06$29482f90$a501280a@phx.gbl...
> 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.
>
>
>
> >-----Original Message-----
> >Hi
> >you could use round in each cell. e.g.
> >=ROUND(A1,-3)
> >
> >
> >-- 
> >Regards
> >Frank Kabel
> >Frankfurt, Germany
> >
> >
> >TKI wrote:
> >> 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.
> >.
> >
Quantcast