Minimizing differences in specified number of subtotal groups

Question

I have data like this:

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 (variable) numbers of items from column C.

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

The order of ColumnC may not be changed.

How might this be accomplished? (VBA will work for me, if needs be.)

@pnuts: yes, because I have to keep to the order of books (column A) and chapters (column B). — user438, Dec 30 '14 at 01:10

pnuts · Answer 1 · 2014-12-31T06:29:10.813

2

This is only approximate:

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

but for the absolute minimum VBA or the like may be required. Replace 3 by the required number of parts.

edited Dec 31 '14 at 06:29

answered Dec 30 '14 at 02:03

pnuts

58,317
11
87
139

1

Thanks (will upvote once I have enough rep). I don't mind a VBA answer, if that'll be more accurate. – user438 Dec 30 '14 at 03:06
Actually, trying this (with 3 replaced by 90) on my dataset doesn't quite do the trick - I end up with 83 subtotals. – user438 Dec 30 '14 at 03:16

Minimizing differences in specified number of subtotal groups

1 Answers1