Precision

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.

PAD

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.

Posted in Uncategorized

4 thoughts on “Precision

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

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

  3. 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
    ‘/========================/

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

Leave a Reply

Your email address will not be published. Required fields are marked *