| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row of numbers adding incorrectly.
I have built a spread sheet that will add individual column of numbers. The last row on each column is the total for that column. (Row 50). The next row (Row 51) is a formula that is as follows: (A50/1.06)*.06 . Row 50 being the Total for each column of numbers. The formula is to take the total for each individual column and divide it by 1.06 then multiply that by .06. I have formatted all numbers in each cell to be (numbers to 2 decimal places). At the end of Row 51 (Lets say Column M) I have added all of the numbers in Row 51. The Total in M51 does not equal the displayed numbers in each of the cells. There is usually .01 to .05 cents difference. I think I know what might be causing this error. It appears to be a rounding error. I do not know how to fix this problem. Any comments or suggestions are much appreciated. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Row of numbers adding incorrectly.
Dale
Formatting only truncates the display, Excel still stores and uses in future calculations the full value, hence the appearance of an incorrect result. You can either use the ROUND function in your formulas to truncate the value of use ToolsOptions...CalculationPrecision as displayed I prefer the first route as it gives more control -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog: www.nickhodge.co.uk/blog/ FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007 www.officeusergroup.co.uk "Dale" wrote in message ... I have built a spread sheet that will add individual column of numbers. The last row on each column is the total for that column. (Row 50). The next row (Row 51) is a formula that is as follows: (A50/1.06)*.06 . Row 50 being the Total for each column of numbers. The formula is to take the total for each individual column and divide it by 1.06 then multiply that by .06. I have formatted all numbers in each cell to be (numbers to 2 decimal places). At the end of Row 51 (Lets say Column M) I have added all of the numbers in Row 51. The Total in M51 does not equal the displayed numbers in each of the cells. There is usually .01 to .05 cents difference. I think I know what might be causing this error. It appears to be a rounding error. I do not know how to fix this problem. Any comments or suggestions are much appreciated. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Row of numbers adding incorrectly.
Hello Nick,
Thanks for your suggestions. Option 2 worked well. I was wondering if you could direct me on how to use the Round function? -- Thanks for your help. Dale "Nick Hodge" wrote: Dale Formatting only truncates the display, Excel still stores and uses in future calculations the full value, hence the appearance of an incorrect result. You can either use the ROUND function in your formulas to truncate the value of use ToolsOptions...CalculationPrecision as displayed I prefer the first route as it gives more control -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog: www.nickhodge.co.uk/blog/ FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007 www.officeusergroup.co.uk "Dale" wrote in message ... I have built a spread sheet that will add individual column of numbers. The last row on each column is the total for that column. (Row 50). The next row (Row 51) is a formula that is as follows: (A50/1.06)*.06 . Row 50 being the Total for each column of numbers. The formula is to take the total for each individual column and divide it by 1.06 then multiply that by .06. I have formatted all numbers in each cell to be (numbers to 2 decimal places). At the end of Row 51 (Lets say Column M) I have added all of the numbers in Row 51. The Total in M51 does not equal the displayed numbers in each of the cells. There is usually .01 to .05 cents difference. I think I know what might be causing this error. It appears to be a rounding error. I do not know how to fix this problem. Any comments or suggestions are much appreciated. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Row of numbers adding incorrectly.
You may want to read Excel's help for =round().
Then post back with your questions. Dale wrote: Hello Nick, Thanks for your suggestions. Option 2 worked well. I was wondering if you could direct me on how to use the Round function? -- Thanks for your help. Dale "Nick Hodge" wrote: Dale Formatting only truncates the display, Excel still stores and uses in future calculations the full value, hence the appearance of an incorrect result. You can either use the ROUND function in your formulas to truncate the value of use ToolsOptions...CalculationPrecision as displayed I prefer the first route as it gives more control -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog: www.nickhodge.co.uk/blog/ FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007 www.officeusergroup.co.uk "Dale" wrote in message ... I have built a spread sheet that will add individual column of numbers. The last row on each column is the total for that column. (Row 50). The next row (Row 51) is a formula that is as follows: (A50/1.06)*.06 . Row 50 being the Total for each column of numbers. The formula is to take the total for each individual column and divide it by 1.06 then multiply that by .06. I have formatted all numbers in each cell to be (numbers to 2 decimal places). At the end of Row 51 (Lets say Column M) I have added all of the numbers in Row 51. The Total in M51 does not equal the displayed numbers in each of the cells. There is usually .01 to .05 cents difference. I think I know what might be causing this error. It appears to be a rounding error. I do not know how to fix this problem. Any comments or suggestions are much appreciated. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Row of numbers adding incorrectly.
Thanks Dave --- I will give that a read.
-- Thanks for your help. "Dave Peterson" wrote: You may want to read Excel's help for =round(). Then post back with your questions. Dale wrote: Hello Nick, Thanks for your suggestions. Option 2 worked well. I was wondering if you could direct me on how to use the Round function? -- Thanks for your help. Dale "Nick Hodge" wrote: Dale Formatting only truncates the display, Excel still stores and uses in future calculations the full value, hence the appearance of an incorrect result. You can either use the ROUND function in your formulas to truncate the value of use ToolsOptions...CalculationPrecision as displayed I prefer the first route as it gives more control -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog: www.nickhodge.co.uk/blog/ FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007 www.officeusergroup.co.uk "Dale" wrote in message ... I have built a spread sheet that will add individual column of numbers. The last row on each column is the total for that column. (Row 50). The next row (Row 51) is a formula that is as follows: (A50/1.06)*.06 . Row 50 being the Total for each column of numbers. The formula is to take the total for each individual column and divide it by 1.06 then multiply that by .06. I have formatted all numbers in each cell to be (numbers to 2 decimal places). At the end of Row 51 (Lets say Column M) I have added all of the numbers in Row 51. The Total in M51 does not equal the displayed numbers in each of the cells. There is usually .01 to .05 cents difference. I think I know what might be causing this error. It appears to be a rounding error. I do not know how to fix this problem. Any comments or suggestions are much appreciated. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding numbers or characters to existing numbers | Excel Worksheet Functions | |||
| adding + and - numbers | Excel Worksheet Functions | |||
| adding odd numbers | Excel Worksheet Functions | |||
| Adding numbers... | Excel Discussion (Misc queries) | |||
| Adding numbers to current numbers | Excel Worksheet Functions | |||
Linear Mode
