Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas and decimal places
Using Excel 2003
I have a spreadsheet with general sum formulas in several columns. All columns are formatted to show 2 decimal points only. When I add up the final column (wth a formula or by blocking/choosing sum on the bottom bar) the total has taken account of all the extra decimal places and added these in thereby giving the wrong total of the column. In some cases the totals also do not add up along the row either because of the same problem! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas and decimal places
When you're summing the final figures you're not summing what you can see but
the actual results of the formulas, so it's taking into account all of the dp's. Surely, you'd want an accurate number for your final sum, no matter what the 'rounded' sums show? HTH. "Dilys Duplock" wrote: Using Excel 2003 I have a spreadsheet with general sum formulas in several columns. All columns are formatted to show 2 decimal points only. When I add up the final column (wth a formula or by blocking/choosing sum on the bottom bar) the total has taken account of all the extra decimal places and added these in thereby giving the wrong total of the column. In some cases the totals also do not add up along the row either because of the same problem! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas and decimal places
Dilys
I strike this problem frequently. Any time one of the sum components contains a calculated figure you are likely to get numbers with more than 2 decimal places. If it is money then actual real amount usually forms part of a greater total and so discrepancies arise. My solution Use the following as your <General Sum Formula =ROUND(<General Sum Formula,2) to get only the decimal places Example Values in B2, C2, D2, E2 Total required in F2 =ROUND(Sum(B2:E2),2) When F2 is used as a component in a greater total there will not be a problem with extra decimal places. Incidentally don't ever compare two figures for equality unless you use something similar to the above first. Where a calculation is involved the system automatically goes out to 23 (I think) places. -- Don C "Dilys Duplock" wrote: Using Excel 2003 I have a spreadsheet with general sum formulas in several columns. All columns are formatted to show 2 decimal points only. When I add up the final column (wth a formula or by blocking/choosing sum on the bottom bar) the total has taken account of all the extra decimal places and added these in thereby giving the wrong total of the column. In some cases the totals also do not add up along the row either because of the same problem! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Decimal Issues | Excel Worksheet Functions | |||
Decimal Places - Settings for Auto Rounding Up/Down | Excel Worksheet Functions | |||
number returns only two decimal places after I change from text | Excel Discussion (Misc queries) | |||
Formula for: Format Decimal places? | Excel Discussion (Misc queries) | |||
DECIMAL PLACES IN FORMULAS | Excel Worksheet Functions |