Total calculation out by .2

[S Findlay]

Hi, having trouble have one column of figures produced by formula when I total up at the bottom of the sheet they are out by .02 do not know how to solve this problem, I have been into format cells to 2 decimal places but still differs.

 

[Norman Harker]

Hi S Findlay!

The problem that you are experiencing is because formatting doesn't
alter the numbers that are stored.

There are various approaches:

You could round the numbers that you are adding rather than just
format them. But this changes what numbers are stored.

You could use a summing formula that adds the formatted rounded
numbers.

Example:
=SUM(ROUND(A1:A6,2))
Entered as an array by pressing and holding down Ctrl + Shift and then
pressing Enter.

However, this is really giving a false answer in that it is not the
sum of the numbers that have been formatted.

You could use:
Tools > Options > Calculation
Check "Precision as displayed"
OK

But this serves to truncate all of the numbers in the workbook to
their current format appearance and that may be undesirable.

Finally, you could live with the problem and annotate your results
appropriately.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/

 

[RagDyeR]

I would guess that your problem is rounding.

Your formula is probably producing numbers that are in excess of 2 decimal
places, but since you've set your format to two places, XL is displaying
*two* places, but XL is calculating *all* the decimals.

When dealing with money, it's always a good idea to wrap your formulas in a
rounding function.

=ROUND(A1*B1,2)

This returns numbers that match exactly what you see displayed.

You could also change the way your WB displays it's numbers by :
<Tools> <Options> <Calculation> tab,
And check "PrecisionAsDisplayed", but this really hinders *exact*
calculations in your WB.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hi, having trouble have one column of figures produced by formula when I
total up at the bottom of the sheet they are out by .02 do not know how to
solve this problem, I have been into format cells to 2 decimal places but
still differs.

 

[Niek Otten]

Hi RagDyerR,

<And check "PrecisionAsDisplayed", but this really hinders *exact*
calculations in your WB>

It doesn't. The option only affects cells which are explicitly formatted. So
if a cell doesn't show many digits because the column is too narrow, it will
still be calculated with maximum precision.
Certainly in financial spreadsheets I find Precision as displayed a very
useful option.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[RagDyeR]

You're right Niek.
I didn't exactly phrase it concisely enough, since I meant to convey that
*other* calculations within the sheet, such as inventory amounts /
depletions, or parts per pallet/case might be adversely affected.

In my business, with some dyestuff running at $400/lb., and the inventory
being kept in Kg., we track to the gram, that's 3 decimals, and depletion is
by individual formula deduction, which goes to mg., that's 5 to 6 decimals.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


Hi RagDyerR,

<And check "PrecisionAsDisplayed", but this really hinders *exact*
calculations in your WB>

It doesn't. The option only affects cells which are explicitly formatted. So
if a cell doesn't show many digits because the column is too narrow, it will
still be calculated with maximum precision.
Certainly in financial spreadsheets I find Precision as displayed a very
useful option.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel