Share this content
0
9
3192

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

Replies

Please login or register to join the discussion.

avatar
21st Feb 2013 12:58

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

 

Thanks (1)
avatar
21st Feb 2013 13:02

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

Thanks (1)
avatar
By ACDWebb
21st Feb 2013 13:20

If you want to add ROUND formulae

your could possibly use the code below. It was written quite a while back and should check 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

Thanks (1)
21st Feb 2013 14:05

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

Thanks (0)
avatar
22nd Feb 2013 13:16

Correct

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

 

Thanks (0)
avatar
22nd Feb 2013 14:59

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

Thanks (0)
avatar
22nd Feb 2013 15:44

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!

Thanks (0)
avatar
22nd Feb 2013 17:46

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

Thanks (0)
22nd Feb 2013 18:28

Rounding the sum

Blackelise wrote:
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

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

Thanks (0)