Minimizing differences in a given number of intermediate groups

Question

I have the following data:

A       B   C   D    
Book1   1   49  
        2   37  
        3   31  
        4   46  163
        5   35  
        6   47  
        7   40  
        8   38  160
        9   45  
        10  25  
        11  38  
        12  29  
        13  25  
        14  18  180

where column D contains subtotals for the (variable) element numbers from column C.

What I'm trying to do, taking into account a given number of these subtotals, should be as close as possible to their average (which may be true for a small sample, but not necessarily in a larger table). In other words, if the total number of elements in column C is 15,000, and I want to divide them into 90 parts, then each of these parts should be as close as possible to 166.67 (15 000/90).

The order of ColumnC cannot be changed.

How can I do that? (VBA will work for me if needed).

Answer 1

:

 =IF(SUM(C$2:C2)-SUM(D$1:D1)>SUM(C:C)/3,SUM(C$2:C2)-SUM(D$1:D1),"")  

VBA . 3 .