“Every fool can know. The point is to understand.” [Albert Einstein]


Rounded values do not necessarily match their rounded sum as you can see here. How can I make sure that the sum of my rounded percentages sums up to 100% exactly? Can I ensure for accounting purposes that my distribution of overhead costs exactly matches the original cost sum? These questions are well known and have been analysed for a long time. This article presents an easy-to-use solution with Excel / VBA. The function presented can round relative values (percentages) to 100% exactly, and it can round absolute values (cost distributions, for example) while preserving their original rounded sum. A parameter drives which type of error versus common half-up rounding you like to minimize: the absolute error, or the relative error.

Percentage Example

The values 11, 45, and 555 with the sum 611 show a percentage sum not of 100.00 but of 99.99 if percentages are rounded to 2 digits - bold values in non-sum cells have been amended by the function RoundToSum:

Values Percent Minimize absolute Error Minimize relative Error
11 1.80 1.80 1.80
45 7.36 7.37 7.36
555 90.83 90.83 90.84
Sum 611 99.99 100.00 100.00

The Excel / VBA function call RoundToSum({11,45,555},2,FALSE) would result in {1.80,7.37,90.83}, though. Here the percentage value 7.364975 was rounded to the “wrong” side to achieve the percentage sum of 100.00 and to minimize the absolute error in comparison to half-up rounding. With RoundToSum({11,45,555},2,FALSE,2) we would have received {1.80,7.36,90.84} because the error type parameter 2 would have minimized the relative error.

Example with Absolute Values

The sum of column 2 differs by +2.000 from the rounded sum. Bold values in non-sum cells have been amended by the function RoundToSum:

Minimize Absolute Error Minimize Relative Error
Values Round to 1,000 RoundToSum (…,-3,…,1) RoundToSum (…,-3,…,2)
4,523 5,000 5,000 5,000
456 0 0 0
-78,845 -79,000 -79,000 -79,000
-14,491 -14,000 -15,000 -14,000
65,789 66,000 66,000 66,000
129,512 130,000 129,000 129,000
15,562 16,000 16,000 16,000
548,555 549,000 549,000 548,000
1,590 2,000 2,000 2,000
-897 -1,000 -1,000 -1,000
6,968 7,000 7,000 7,000
2,987 3,000 3,000 3,000
Sum 681,709 684,000 682,000 682,000

Example of a more Complex Application: Allocation of Overheads

See Allocation of Overheads.

Comparison of RoundToSum versus Other “Simpler” Methods

See RoundToSum (VBA) vs Other Methods.

Comparison of RoundToSum versus sbDHondt

RoundToSum implements the Hare-Niemeyer method which in some way is superior to the D’Hondt method when you need to distribute parliament seats in a fair manner. See sbDHondt.

Example of how to Deal with the Alabama Paradoxon: Fair Staff Selection based on Team Size

See Fair Staff Selection for Special Tasks based on Team Sizes.

Another Practical Example for the Exact Relation of Random Numbers

See sbExactRandHistoGrm (VBA).

The Excel / VBA Function RoundToSum


RoundToSum – Rounding values preserving their rounded sum


RoundToSum(vInput, [lDigits], [bAbsSum], [lErrorType], [bDontAmend])


RoundToSum rounds summands without changing their rounded sum. It applies the largest remainder method to minimize the error versus the commonly used half-up rounding method. In case this error is identical for one or more summands the first summand(s) encountered will be amended.

Please note: The solution presented here is limited to one-dimensional tables without subtotals. For tables of higher dimensions or for tables with subtotals there exists no general solution.


vInput – Range or array containing the unrounded input values (summands).

lDigits – Optional, default value is 2. Number of digits to round to. For example: 0 rounds to integers, 2 rounds to the cent, -3 will use thousands.

bAbsSum – Optional, default value is TRUE. TRUE takes the summands as they are. FALSE works on the summands' percentages to make all percentages add up to 100% exactly.

lErrorType – Optional, default value is 1. Type of error to be minimized: 1 – absolute error; 2 – relative error).

bDontAmend – Optional, default value is FALSE. TRUE does not amend the rounded summands to match the rounded sum. FALSE performs amendments as described above. This parameter is mainly for ease of use in presentations to see this function’s impact.


Diaconis, P., & Freedman, D. (2007, July 13). Diaconis, P., & Freedman, D. (2007, July 13). (External link!) On Rounding Percentages.

Sande, G. (2005, August 7). (External link!) Guaranteed Controlled Rounding for Many Totals in Multi-way and Hierarchical Tables.

Appendix – RoundToSum Code

Please read my Disclaimer.

Option Explicit

Enum mc_Macro_Categories
    mcFinancial = 1
    mcSecond_custom_category 'and so on
End Enum 'mc_Macro_Categories

Function RoundToSum(vInput As Variant, Optional lDigits As Long = 2, Optional bAbsSum As Boolean = True, _
    Optional lErrorType As Long = 1, Optional bDontAmend As Boolean = False) As Variant
'Calculate rounded summands which exactly add up to the rounded sum of unrounded summands.
'It uses the largest remainder method which minimizes the error to the original unrounded summands.
'V2.0 PB 26-Nov-2022 (C) (P) by Bernd Plumhoff
Dim i As Long, j As Long, k As Long, n As Long, lCount As Long, lSgn As Long
Dim d As Double, dDiff As Double, dRoundedSum As Double, dSumAbs As Double: Dim vA As Variant
With Application.WorksheetFunction
vA = .Transpose(.Transpose(vInput)): On Error GoTo Errhdl: i = vA(1) 'Force error in case of vertical arrays
On Error GoTo 0: n = UBound(vA): ReDim vC(1 To n) As Variant, vD(1 To n) As Variant: dSumAbs = .Sum(vA)
For i = 1 To n
    d = IIf(bAbsSum, vA(i), vA(i) / dSumAbs * 100#): vC(i) = .Round(d, lDigits)
    If lErrorType = 1 Then 'Absolute error
        vD(i) = vC(i) - d
    ElseIf lErrorType = 2 Then 'Relative error
        vD(i) = (vC(i) - d) * d
        RoundToSum = CVErr(xlErrValue): Exit Function
    End If
Next i
If Not bDontAmend Then
    dRoundedSum = .Round(IIf(bAbsSum, dSumAbs, 100#), lDigits)
    dDiff = .Round(dRoundedSum - .Sum(vC), lDigits)
    If dDiff <> 0# Then
        lSgn = Sgn(dDiff): lCount = .Round(Abs(dDiff) * 10 ^ lDigits, 0)
        'Now find highest (lowest) lCount indices in vC
        ReDim m(1 To lCount) As Long
        For i = 1 To lCount: m(i) = i: Next i
        For i = 1 To lCount - 1
            For j = i + 1 To lCount
                If lSgn * vD(i) > lSgn * vD(j) Then k = m(i): m(i) = m(j): m(j) = k
            Next j
        Next i
        For i = lCount + 1 To n
            If lSgn * vD(i) < lSgn * vD(m(lCount)) Then
                j = lCount - 1
                Do While j > 0
                    If lSgn * vD(i) >= lSgn * vD(m(j)) Then Exit Do
                    j = j - 1
                For k = lCount To j + 2 Step -1: m(k) = m(k - 1): Next k: m(j + 1) = i
            End If
        Next i
        For i = 1 To lCount: vC(m(i)) = .Round(vC(m(i)) + dDiff / lCount, lDigits): Next i
    End If
End If
RoundToSum = vC
If TypeName(Application.Caller) = "Range" Then
    If Application.Caller.Rows.Count > Application.Caller.Columns.Count Then
        RoundToSum = .Transpose(vC) 'It's two-dimensional with 2nd dim const = 1
    End If
End If
Exit Function
'Transpose variants to be able to address them with vA(i), not vA(i,1)
vA = .Transpose(vA): Resume Next
End With
End Function

Sub DescribeFunction_sbRoundToSum()
'Run this only once, then you will see this description in the function menu
Dim FuncName As String, FuncDesc As String, Category As String, ArgDesc(1 To 5) As String
FuncName = "RoundToSum"
FuncDesc = "Rounding values preserving their rounded sum"
Category = mcMath_and_Trig
ArgDesc(1) = "Range or array which contains unrounded values"
ArgDesc(2) = "[Optional = 2] Number of digits to round to. For example: 0 rounds to integers, 2 rounds to the cent, -3 will use thousands"
ArgDesc(3) = "[Optional = True] True takes the summands as they are; False works on the summands' percentages to make all percentages add up to 100% exactly"
ArgDesc(4) = "[Optional = 1] Error type: 1= absolute error, 2 = relative error"
ArgDesc(5) = "[Optional = False] True does not amend the rounded summands to match the rounded sum; False performs the calculation as described"
Application.MacroOptions _
    Macro:=FuncName, _
    Description:=FuncDesc, _
    Category:=Category, _
End Sub


Please read my Disclaimer.

roundtosum.xlsm [63 KB Excel file, open and use at your own risk]