Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting and adding cells formatted to 2 decimal places
Is there any way that I can "Autosum" a column which has numbers formatted to
2 decimal places - were the "Autosum" adds the numbers as shown in the cell (not the numbers with full decimals). e.g. 1.2345 will show as 1.23 but when added includes the,0045 and shows an answer which does not add up according to the info displayed? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting and adding cells formatted to 2 decimal places
Hi Colin,
Formatting numbers to 2 decimal places doesn't truncate the actual data, only the displayed data. All decimal places (up to 16, I think) are remembered by XL, and always used in calculations. Regards - Dave. "Colin" wrote: Is there any way that I can "Autosum" a column which has numbers formatted to 2 decimal places - were the "Autosum" adds the numbers as shown in the cell (not the numbers with full decimals). e.g. 1.2345 will show as 1.23 but when added includes the,0045 and shows an answer which does not add up according to the info displayed? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting and adding cells formatted to 2 decimal places
Hi,
It's always struck me as fundamentally flawed to do a calculation with the deliberate intention of getting an incorrect answer but anyway try this Tools|Options|calculation select precision as shown Excel will display a warning that your answer may be incorrect which you must accept. Now do your autosum and only displayed decimal places will be summed. warning this is global and afffects all calculations another way =SUMPRODUCT(LEFT(A1:A2,4)+0) works for your posted example but will go wrong if you try to add 1.2678 + 21.12345 because it takes the first 4 characters including the decimal point. Mike "Colin" wrote: Is there any way that I can "Autosum" a column which has numbers formatted to 2 decimal places - were the "Autosum" adds the numbers as shown in the cell (not the numbers with full decimals). e.g. 1.2345 will show as 1.23 but when added includes the,0045 and shows an answer which does not add up according to the info displayed? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting and adding cells formatted to 2 decimal places
You could use the ROUND fuction to round the cells to 2 DP
You could also use the ToolsOptionsCalculationPrecision as displayed. Note the PAD changes all the numbers forever, no turning back. Gord Dibben MS Excel MVP On Mon, 5 May 2008 08:30:01 -0700, Colin wrote: Is there any way that I can "Autosum" a column which has numbers formatted to 2 decimal places - were the "Autosum" adds the numbers as shown in the cell (not the numbers with full decimals). e.g. 1.2345 will show as 1.23 but when added includes the,0045 and shows an answer which does not add up according to the info displayed? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Global percentage decimal places default formatting | Excel Discussion (Misc queries) | |||
conditonally formatting decimal places | Excel Discussion (Misc queries) | |||
Help with formating cells (decimal places) | Excel Worksheet Functions | |||
Can you set to default the # of decimal places in Format Cells? | Setting up and Configuration of Excel | |||
Adding numbers in a column that have 3 decimal places | Excel Worksheet Functions |