There is an option under Tools > Options called *Precision As Displayed*. Choosing this option changes the values in your cells to match the formatting of the cell.

For instance, if you have 41.234 in a cell and the cell is formatted to two decimal places (so it shows 41.23), this option will actually change the number to 41.23.

Back when I was a practicing CPA, we always had the problem of financial statements being one dollar off (the balance sheet must balance, you know). Nobody really cares about $1, but it’s just shoddy workmanship to produce a financial statement that doesn’t balance. So we would just plug a buck in somewhere and all would be well. My boss was a stickler about the Precision As Displayed option. It had to be on for financial statement workbooks or you would get a tongue lashing. Actually, he wanted it on for every workbook, but I don’t swing that way.

For financial statements, I suppose it’s not a bad policy. I’m really uncomfortable having a change in formatting change actual values. For that reason, I never use this option. I’m careful to include the ROUND function where necessary and that takes care of any problems.

Note also that the once Precision As Displayed is chosen, the actual numbers are gone forever. You can’t ever Undo them. Heed the warning.

I’m with you, Dick. I don’t feel comfortable having Excel change stuff like that, especially if you decide 30 minutes later that you don’t actually WANT it formatted that way.

I use the ROUND() function extensively for that reason. I don’t always round to the same number of decimals, either; I use 0, 2 and 4 decimals pretty equally. I got sick of adding the function by hand, so I wrote some code that would do it for me in a snap. I’m posting it below:

Sub rounder(arg As Integer)

Dim cell As Range

Dim short_form As String, long_form As String

On Error GoTo Error

For Each cell In Selection

If cell.Formula <> “” Then

If (Left(cell.Formula, 1)) = “=” Then

If (Mid(cell.Formula, 2, 5)) = “ROUND” Then

short_form = Mid(cell.Formula, 8, Len(cell.Formula) – 10)

Else

short_form = Right(cell.Formula, (Len(cell.Formula) – 1))

End If

Else

short_form = cell.Formula

End If

long_form = “=round(” & short_form & “,” & arg & “)”

cell.Formula = long_form

End If

Next

Exit Sub

Error:

Exit Sub

End Sub

It’s called using these bits of code (which I call from a custom toolbar I created):

Sub round4()

Call rounder(4)

End Sub

Sub round2()

Call rounder(2)

End Sub

Sub round0()

Call rounder(0)

End Sub

And if I want to remove the rounding feature, I have this code:

Sub remove_round()

Dim cell As Range

Dim short_form As String, long_form As String

On Error GoTo Error

For Each cell In Selection

If cell.Formula <> “” Then

If (Left(cell.Formula, 1)) = “=” Then

If (Mid(cell.Formula, 2, 5)) = “ROUND” Then

short_form = Mid(cell.Formula, 8, Len(cell.Formula) – 10)

long_form = “=” & short_form

cell.Formula = long_form

End If

End If

End If

Next

Exit Sub

Error:

Exit Sub

End Sub

I’m not sure if this is good code, but it saves me time every day. Time that can be better spent perusing the goldmine that is DDOE!

Rounding errors are prevalent in most SS. I’m sure I’m not telling you guys anything you donĂt know already, but it’s a problem of number function – I think that like you say, to be as accurate and precise you have to design your SS well, rounding where Nescafe (sorry necessary), but you can never really remove the errors.

As an Accountant I also agree and have written some code to save time, so here’s my 2 cents…

‘/========================/

Sub Rounding_Wrap()

‘Wrap ‘Round’ function with user decided decimals around numbers _

and formulas only

Dim blnNumber As Boolean

Dim objCell As Object, objInput As Object

Dim strOriginalAddress As String

Dim strvarInputAddress As String

Dim strOrigCalcStatus As String

Dim varInput As Variant

Dim varRounding As Variant

On Error GoTo err_Sub

‘save calculation setting

Select Case Application.Calculation

Case xlCalculationAutomatic

strOrigCalcStatus = “Automatic”

Case xlCalculationManual

strOrigCalcStatus = “Manual”

Case xlCalculationSemiautomatic

strOrigCalcStatus = “SemiAutomatic”

Case Else

strOrigCalcStatus = “Automatic”

End Select

blnNumber = False

strOriginalAddress = Selection.Address

Set varInput = _

Application.InputBox(prompt:= _

“Select Range of Numbers to be rounded “, _

Default:=strOriginalAddress, Type:=8)

‘get how many cells have been selected and location

strvarInputAddress = varInput.Address

‘check to see if any cells have been selected

If varInput.Count = 0 Then

MsgBox “Nothing Selected…” & vbCr & vbCr & _

“Rounding procedure discontinued…”, _

vbExclamation + vbOKOnly, “Warning…”

GoTo exit_Sub

End If

‘ask for rounding precision

varRounding = _

Application.InputBox(prompt:= _

“Enter Number of places for cells to be rounded “, _

Default:=2, Type:=1)

‘check to see if a number was entered for rounding precision

If Asc(varRounding) <> 48 Then

If Len(varRounding) = 0 Or varRounding > 100 _

Or varRounding = False Then

MsgBox “Rounding Amount Problem Encountered…” & _

vbCr & vbCr & _

“Rounding procedure discontinued…”, _

vbExclamation + vbOKOnly, “Warning…”

GoTo exit_Sub

End If

End If

‘only look in used area of the worksheet

Set varInput = Intersect(varInput.Parent.UsedRange, varInput)

‘create round formula

Application.Calculation = xlManual

For Each objCell In varInput

‘check if cell is a value

Select Case UCase(TypeName(objCell.value))

Case “BYTE”

blnNumber = True

Case “CURRENCY”

blnNumber = True

Case “DECIMAL”

blnNumber = True

Case “DOUBLE”

blnNumber = True

Case “INTEGER”

blnNumber = True

Case “LONG”

blnNumber = True

Case “SINGLE”

blnNumber = True

Case Else

blnNumber = False

End Select

‘if cell is a value in the used area, continue

If blnNumber = True Then

Select Case Asc(Left(objCell.Formula, 1))

Case 45 ‘minus (-) sign

objCell.Formula = _

“=ROUND(” & _

objCell.Formula & “,” & varRounding & “)”

Case 48 To 57 ‘numbers 0 thru 9

objCell.Formula = _

“=ROUND(” & _

objCell.Formula & “,” & varRounding & “)”

Case 61 ‘equal (=) sign

objCell.Formula = _

“=ROUND(” & _

Right(objCell.Formula, _

Len(objCell.Formula) – 1) & _

“,” & varRounding & “)”

End Select

End If

Next objCell

exit_Sub:

Set varInput = Nothing

‘re-set to original calculation method

Select Case strOrigCalcStatus

Case “Automatic”

Application.Calculation = xlCalculationAutomatic

Case “Manual”

Application.Calculation = xlCalculationManual

Case “SemiAutomatic”

Application.Calculation = xlCalculationSemiautomatic

Case Else

Application.Calculation = xlCalculationAutomatic

End Select

Exit Sub

err_Sub:

MsgBox “Error encountered…” & _

vbCr & vbCr & _

“Rounding procedure discontinued…”, _

vbExclamation + vbOKOnly, “Warning…”

GoTo exit_Sub

End Sub

‘/========================/

I happened to stumble across this post, and this seems to be just what I was looking for! I’d like to try these, but am having problems integrating code into my w/s. I was trying to test by including code with a click button, but the VB screen doesn’t take simply cutting/pasting code in. I think it’s getting hung up on the underscores, quotes, and returns. I’ve tried deleting the underscores and replacing quotes with ones from my own keyboard, and looked like it was at least accepting the code, but am really having a problem with the ” ‘if cell is a value in the used area, continue ” section. It’s making a lot of the code red (which means there’s an compile error?).I’ve taken a VB course about 6 years ago and am very rusty, but remember a few things.

Any suggestions with cutting and pasting the two examples above?