Simple Excel problem with rounding

In Excel if I have a formula in each of three cells (say, B1, B2 and B3), let's say B1 has =A1/3, B2 has =A1*2, and B3 has =A1.

Say in A1 I have 4.2.

Then B1, B2 and B3 evaluate to 1.4, 8.4 and 4.2 respectively.

Then =sum(B1:B3) evaluates to 14.

Suppose then I set my viewing options to display only whole numbers.

I then have 1+8+4=14.

Other users of my spreadsheet will spot what appears to be an obvious error!

I could avoid this by laboriously amending the formula in each of the cells to =round([formula],0).

I would then have a different result 1+8+4=13 which at least doesn't 'look wrong' on screen.

But changing the formulae in each of the cells B1 to B3 is a pain!  And what if I have a spreadsheet with hundreds of formulae?

Is there an easy way to make sure a spreadsheet which shows figures in round pounds, or round thousands, or round millions, can be made to look as if at least I can add up correctly!!

I am sure this must have been asked before.


There are 9 comments. Login or register to view them.

Precision as displayed

WhichTyler |

Copy & Paste?

patvanaalst |
patvanaalst's picture

If you want to add ROUND formulae

ACDWebb |
ACDWebb's picture

Thanks for the replies

runningmate |
runningmate's picture


WhichTyler |

Another way?

Andrew Thain |


duncanphilpstate |

Rounding on sum

Blackelise |

Rounding the sum

runningmate |
runningmate's picture