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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|