How to round a list of decimals in Excel, so that the sum of the whole numbers equal a defined total?

user_14211623 0
Viewed 763

Having difficulty developing an excel function that will round a list of decimals, so that the sum of the whole numbers equals the original or a defined total.

Edit I guess one way to do it is writing a function that first searches for the largest numbers and rounds them to the nearest whole number. That whole number is then counted and the function moves on to the next, until the total count equals the target total.

The problem that I am running into is that if there are too many numbers that are closer to 0, then the function will never equal the target total. So what the function then needs to do is identify the largest decimals, round them up, count, and then move on to the next until the sum of the count is equal to the target total. The left over data can then round to 0.

Sorry, I hope this is clearer....

I am dealing with larger data sets where the totals of the rounded whole numbers have much larger deviations to the original total.

It would preferable if this could be accomplished with an excel function, otherwise I am also open to doing in VBA.

Thanks!

Edit 3: Here is an example data set:

enter image description here

rounding vba excel
2 Answers

I'd agree with @pghcpa this is rather an arithmetic problem. One idea for solution:

  1. order the numbers descending on their fraction part
  2. take the floor of each (i.e ignoring the fractions)
  3. take the sum of those floors
  4. compare that sum to the desired sum, take the difference This way you'd probably have a positive difference, so you can start from the top and add 1 to each number downwards, all until the difference is gone.
user_141991680

Please study my project VBA.Round.

Browse to paragraph Rounding a series of numbers to a sum

Code is way too much to post here, but an example workbook is included for download.

Example:

This function will read the range of distribution values, round the sum, and fill the two ranges with rounded values of 2 and zero decimals, totalling to the requested total (confirmed, as seen, by the formula):

' Practical example for using Excel ranges for RoundSum
'
' Source URL:
'   https://stackoverflow.com/questions/63715043/how-to-round-a-list-of-decimals-in-excel-so-that-the-sum-of-the-whole-numbers-e
'
' 2020-09-14. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub RoundDistribution()

    ' Named ranges. These should pairwise match in row size.
    Const VolumeName        As String = "Volume"
    Const PercentValuesName As String = "Percent_Distribution"
    Const ValuesName        As String = "Distribution"
    Const RoundedValuesName As String = "Rounded_Distribution"
    
    Dim Range       As Excel.Range
    
    Dim Values()    As Currency
    Dim Results()   As Currency
    
    Dim Total       As Integer
    Dim Index       As Integer
    
    ' Read percent distribution values from the named range.
    Set Range = ThisWorkbook.Names(PercentValuesName).RefersToRange
    ' Read original volume value.
    Total = ThisWorkbook.Names(VolumeName).RefersToRange(1, 1)
    
    ' Dim input and output arrays.
    ReDim Values(1 To Range.Rows.Count)
    ReDim Results(1 To Range.Rows.Count)
    
    ' Fill input array.
    For Index = LBound(Values) To UBound(Values)
        Values(Index) = Range(Index, 1)
    Next
    
    ' Round total and retrieve array with distribution values.
    Results = RoundSum(Values, RoundMid(Total), 2)
    
    ' Fill named range with distribution values.
    For Index = LBound(Results) To UBound(Results)
        ThisWorkbook.Names(ValuesName).RefersToRange(Index, 1) = Results(Index)
    Next
    
    ' Round total and retrieve array with rounded distribution values.
    Results = RoundSum(Values, RoundMid(Total))
    
    ' Fill named range with rounded distribution values.
    For Index = LBound(Results) To UBound(Results)
        ThisWorkbook.Names(RoundedValuesName).RefersToRange(Index, 1) = Results(Index)
    Next
    
End Sub

Output:

enter image description here

Note please, that the function is capable of rounding to any number of decimals, and to select one instance only of the values 0.34 to obtain a match.

The full demo (Excel workbook) and code is still for download on GitHub.

user_35272970
Related