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 (9)
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: https://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 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
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