A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Total calculation out by .2



 
 
Thread Tools Display Modes
  #1  
Old April 24th, 2004, 01:06 PM
S Findlay
external usenet poster
 
Posts: n/a
Default Total calculation out by .2

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.
  #2  
Old April 24th, 2004, 01:29 PM
Norman Harker
external usenet poster
 
Posts: n/a
Default Total calculation out by .2

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


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/


  #3  
Old April 24th, 2004, 01:40 PM
RagDyeR
external usenet poster
 
Posts: n/a
Default Total calculation out by .2

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!
==============================================

"S Findlay" wrote in message
...
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.


  #4  
Old April 24th, 2004, 02:41 PM
Niek Otten
external usenet poster
 
Posts: n/a
Default Total calculation out by .2

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" wrote in message
...
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!
==============================================

"S Findlay" wrote in message
...
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.




  #5  
Old April 24th, 2004, 07:23 PM
RagDyeR
external usenet poster
 
Posts: n/a
Default Total calculation out by .2

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 !
--------------------------------------------------------------------


"Niek Otten" wrote in message
...
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" wrote in message
...
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!
==============================================

"S Findlay" wrote in message
...
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.





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:43 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.