Formatting and adding cells formatted to 2 decimal places

[Colin]

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?

 

[Dave]

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.

 

[Mike H]

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

 

[Gord Dibben]

You could use the ROUND fuction to round the cells to 2 DP

You could also use the Tools>Options>Calculation>Precision as displayed.

Note the PAD changes all the numbers forever, no turning back.


Gord Dibben MS Excel MVP