Formatting/calculations question

C

Charlotte Howard

Hello,

I would like to be able to round the data in the table below to 2dp, this
however, can throw the calculations out for the totals. Is there any way
that I can have the cell display only 2dp, but the calculations be preformed
on the entire figure?

In the example below, the total figure is 1266.128 - if I format cells to
show 2dp, it changes to 1226.13,

I am using Excel 2003.

A2 B2 C2
TV 3% of TV 8.5% of TV A2-B2-C2
437.96 13.139 37.227 387.596
165.04 4.95 14.03 146.06
396.84 11.91 33.73 351.20
385.62 11.57 32.78 341.27
TOTAL
1226.128


Thanks for help

Charlotte
 
M

Mike H

Charlotte,

Formatting the number doesn't change it's underlying value. To demonstrate
this put a number in a cell (Say) 1.4 and format it as a number with no
decimal places and the number will display as 1.

In another cell multiply the 1.4 * 2 and you will get the correct answer of
2.8.

Mike
 
P

Pete_UK

Formatting does not change the values in the cells - merely how they
are displayed. So why don't you apply a format of 2dp to all of the
cells except for the total?

I presume you meant that 1266.128 becomes 1266.13 and not 1226.13.

Hope this helps.

Pete
 
C

Charlotte Howard

Hi,
It turns out that the person who asked me about this wanted something the
other way around.

So I used the ROUND function to 2dp, and he now believes he has all his data
correct. I did try to point out that Maths is very accurate, and that the
more decimal places you use the more accurate things are.

He didn't like the 1*2 = 2.8 example at all!

Thanks!
 
Top