Rounding Stored vs. On-the-Fly Values

Question

Consider the average of four numbers {4.1, 4.1, 4.1, 4.3}, Dutch rounded to one decimal place.

The average of the four numbers is exactly 4.15; when using VBA's Round function it correctly returns 4.2. However, if I give it the expression that calculates the average it rounds incorrectly: Round((4.1 + 4.1 + 4.1 + 4.3)/4, 1) returns 4.1. I can coax a correct answer by doing something like Round(Round((4.1 ...)/4), 5), 1) so I suspect it has something to do with how results of expressions are stored. I also tried multiplying each number by 10 then dividing by 40, but the rounding was still incorrect.

? (4.1 + 4.1 + 4.1 + 4.3)/4
 4.15 
? Round(4.15, 1)
 4.2 
? Round((4.1 + 4.1 + 4.1 + 4.3)/4, 1)
 4.1
? Round(Round((4.1 + 4.1 + 4.1 + 4.3)/4, 5), 1)
 4.2  
? Round((4.1*10 + 4.1*10 + 4.1*10 + 4.3*10)/40, 1)
 4.1 

Question: In Excel/VBA, what's the difference between being given a float directly and being given an expression that evaluates as a float?

Answer 1

? Round((4.1 + 4.1 + 4.1 + 4.3) / 4, 1)
 4.1 

This is because you are adding floating point numbers combined with the buggy Round.

You can trick those values to become true decimals by forcing that on only one factor or addend, and Round will round to even, for example:

? Round((4.1 + 4.1 + 4.1 + 4.3) / CDec(4), 1)
 4.2 

You can also use Format, which does true 4/5 rounding:

? CDbl(Format((4.1 + 4.1 + 4.1 + 4.3) / 4, "0.0"))
 4.2 

To overcome these quirks completely, you can use my rounding functions, here RoundMid which also does true 4/5 rounding:

? RoundMid((4.1 + 4.1 + 4.1 + 4.3) / 4, 1)
 4.2 

' Rounds Value by 4/5 with count of decimals as specified with parameter NumDigitsAfterDecimal.
'
' Rounds to integer if NumDigitsAfterDecimal is zero.
'
' Rounds correctly Value until max/min value limited by a Scaling of 10
' raised to the power of (the number of decimals).
'
' Uses CDec() to prevent bit errors of reals.
'
' Execution time is about 1µs.
'
' 2018-02-09. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RoundMid( _
    ByVal Value As Variant, _
    Optional ByVal NumDigitsAfterDecimal As Long, _
    Optional ByVal MidwayRoundingToEven As Boolean) _
    As Variant

    Dim Scaling     As Variant
    Dim Half        As Variant
    Dim ScaledValue As Variant
    Dim ReturnValue As Variant
    
    ' Only round if Value is numeric and ReturnValue can be different from zero.
    If Not IsNumeric(Value) Then
        ' Nothing to do.
        ReturnValue = Null
    ElseIf Value = 0 Then
        ' Nothing to round.
        ' Return Value as is.
        ReturnValue = Value
    Else
        Scaling = CDec(Base10 ^ NumDigitsAfterDecimal)
        
        If Scaling = 0 Then
            ' A very large value for NumDigitsAfterDecimal has minimized scaling.
            ' Return Value as is.
            ReturnValue = Value
        ElseIf MidwayRoundingToEven Then
            ' Banker's rounding.
            If Scaling = 1 Then
                ReturnValue = Round(Value)
            Else
                ' First try with conversion to Decimal to avoid bit errors for some reals like 32.675.
                ' Very large values for NumDigitsAfterDecimal can cause an out-of-range error when dividing.
                On Error Resume Next
                ScaledValue = Round(CDec(Value) * Scaling)
                ReturnValue = ScaledValue / Scaling
                If Err.Number <> 0 Then
                    ' Decimal overflow.
                    ' Round Value without conversion to Decimal.
                    ReturnValue = Round(Value * Scaling) / Scaling
                End If
            End If
        Else
            ' Standard 4/5 rounding.
            ' Very large values for NumDigitsAfterDecimal can cause an out-of-range error when dividing.
            On Error Resume Next
            Half = CDec(0.5)
            If Value > 0 Then
                ScaledValue = Int(CDec(Value) * Scaling + Half)
            Else
                ScaledValue = -Int(-CDec(Value) * Scaling + Half)
            End If
            ReturnValue = ScaledValue / Scaling
            If Err.Number <> 0 Then
                ' Decimal overflow.
                ' Round Value without conversion to Decimal.
                Half = CDbl(0.5)
                If Value > 0 Then
                    ScaledValue = Int(Value * Scaling + Half)
                Else
                    ScaledValue = -Int(-Value * Scaling + Half)
                End If
                ReturnValue = ScaledValue / Scaling
            End If
        End If
        If Err.Number <> 0 Then
            ' Rounding failed because values are near one of the boundaries of type Double.
            ' Return value as is.
            ReturnValue = Value
        End If
    End If
    
    RoundMid = ReturnValue

End Function

Full code at VBA.Round.