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

## Replies

## Please login or register to join the discussion.

Precision as displayed

Use the 'precision as displayed' feature in Excel options, but it's quite an 'all or nothing' approach, so save a copy first. And if you are doing financial accounts, your balance sheet may slip out of balance...

Help is here: http://www.accountingweb.co.uk/topic/excel/understanding-excel-rounding-...

Copy & Paste?

Or drag?

You'd only need to change three formulae then just copy them down the columns.

Of course, the downside is that your roundings will multiply out incorrectly and the final end result could be a ways off the real number!

The alternative might just to be to put a disclaimer in the file?

Cheers

Pat

If you want to add ROUND formulae

your

couldpossibly use the code below. It was written quite a while back andshouldcheck through each cell in a selected range and add the ROUND / ROUNDUP / ROUNDDOWN formula to the specified number of decimal places to any cell containing a formula.!!DO TAKE A COPY FIRST JUST IN CASE'Code Starts

Sub RoundManyDown()

Dim aCell As Object

Dim tmp As Byte

tmp = InputBox(prompt:="Enter number of decimal places required: ", _

Title:="SET DECIMAL PLACES", _

Default:=0)

For Each aCell In ActiveWindow.RangeSelection.Cells

If aCell.HasFormula Then Do_Round "ROUNDDOWN", aCell, tmp

Next aCell

End Sub

Sub RoundManyup()

Dim aCell As Object

Dim tmp As Byte

tmp = InputBox(prompt:="Enter number of decimal places required: ", _

Title:="SET DECIMAL PLACES", _

Default:=0)

For Each aCell In ActiveWindow.RangeSelection.Cells

If aCell.HasFormula Then Do_Round "ROUNDUP", aCell, tmp

Next aCell

End Sub

Sub RoundMany()

Dim aCell As Object

Dim tmp As Byte

tmp = InputBox(prompt:="Enter number of decimal places required: ", _

Title:="SET DECIMAL PLACES", _

Default:=0)

For Each aCell In ActiveWindow.RangeSelection.Cells

If aCell.HasFormula Then Do_Round "ROUND", aCell, tmp

Next aCell

End Sub

Sub RemoveRounding()

Dim aCell As Object

Dim tmp, fnda, fndb

For Each aCell In ActiveWindow.RangeSelection.Cells

If IsNumeric(aCell.Value) Then

tmp = aCell.FormulaR1C1

If InStr(1, tmp, "ROUND") > 0 Then

fnda = InStr(1, tmp, "(")

fndb = InStr(1, tmp, ",")

tmp = Mid(tmp, fnda + 1, fndb - fnda - 1)

aCell.FormulaR1C1 = "=" & tmp

End If

End If

Next aCell

End Sub

Sub Do_Round(RndType As String, Optional cAddress As Range, Optional DecPlaces As Byte)

Dim tmp

If Not IsNumeric(DecPlaces) Then DecPlaces = 0

tmp = cAddress.FormulaR1C1

If Left(tmp, 1) = "=" Then tmp = Mid(tmp, 2)

cAddress.FormulaR1C1 = "=" & RndType & "(" & tmp & "," & DecPlaces & ")"

Finish:

End Sub

'Code Ends

## 80_back_in_5_minutes.jpg

Thanks for the replies

@WhichTyler I had a vague memory of there being a way of doing this - thanks for locating it! Presumably this means that each value is truncated based on the display properties OF THAT PARTICULAR CELL?

@patvanaalst Copy and paste can help - but where the spreadsheet is complex it would still be a pain!

@ACDWebb Phew!!!

RM

Correct

What you see (on screen) is what you get, so be careful with percentages, currencies etc.

Another way?

The other option is create an exact replica of the file on a different sheet and the do the =ROUND(sheet1,0) formula and copy where relevant. This will allow you have the orgiinal data along with one which "adds" up when looking at it on paper. A simple check box at the bottom whould ensure that you would always know how far way you are from the "real" total and you could even introduce a roundings line.

Hope this helps

Disclaimer?

I saw an example spreadsheet the other day where the author had put a warning under the totals saying something like "Due to roundings for presentation, displayed values may not add precisely to displayed totals." I thought that was rather neat as a solution.

I don't know how often I've had to fiddle around (in Powerpoint usually) just to make rounded numbers appear to add up correctly so that partners at the back of the room won't miss the whole point of the slide because they're sulking about the total being out by 1 instead of looking at what's going on. And these are supposedly sophisticated people!

Rounding on sum

Surely one should not be correcting by rounding on each line and then sum the column as this may lead to a large disrecancy as mentioned above.

What one should do is to perform rounding on the sum result.

HTH

## 80_back_in_5_minutes.jpg

Rounding the sum

I take the point that rounding numbers which are then multiplied could lead to a significant mis-statement.

But rounding just the sum will not achieve the desired result of a spreadsheet which looks as if it adds up correctly!

In the example given initially the sum of 1+8+4=14 will be the outcome if only the total (which is actually 14.0) is rounded.

RM

## Please login or register to join the discussion.