Simple Excel problem with rounding | AccountingWEB

# 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.

RM

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

|

|

|

|

|

|

|

|

|
• 28 1
• 241 3
• 20 1
• 65 1
• 495 8
• 944 13
• 652 4
• 169 5
• 383 12
• 792 19
• 325 4
• 269 7
• 1,507 29
• 354 5
• 392 12
• 202 1
• 185 2
• 447 13
• 531 1
• 296 8
• 730
• 431
• 371
• 355
• 282
• 260
• 249
• 232
• 210
• 195