1 Column, 1254 cells, Create buckets of summed amounts

[Beetlestone]

I put this on a smaller scale of 28 rows to describe it easier.
This cannot be a macro (VBA), has to be a good old fashioned formula

I have 1 column (A) with 1254 cells, each cell contains tonnage. In column B, I am trying to create "buckets" of tonnage from column A that sum up to just below 10,000, and also distribute the same number of cells in each "bucket".

That means I cant simply add up consecutive cells (ex:B1 - B12 results in 9,841). Rather I need to efficiently distribute the tonnage so that each group (or bucket) has a similar number of cells in it.

Below column B is the result I am trying to achieve, any help on this is greatly appreciated!

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl65, width: 64"][TABLE="width: 296"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Tonnage[/TD]
[TD]Bucket[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]634.9140625[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1729.05957[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]931.9863281[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1076.258789[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]417.0605469[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]332[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]601[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]827.4052734[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]494[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2153[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]644.5859375[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]313[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]2084[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]1753.676758[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]1376.464844[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]5.655273438[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]1570[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]423[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]199[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]2122[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]1091.449219[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]2527[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]178[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]539.9375[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]699.5888672[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[BiocideJ]

I'm not saying what you are asking is impossible, but with formulas, I think it would be pretty near it.

The only route I can think of is to create a separate table where you sort the tonnages and bucket 1 would have the highest + lowest + ... + ... until tonnage is more or less reached.

However, the part that makes it nearly impossible (in my mind) is there would be no way to know in the beginning how many buckets there would need to be or even how many items would be in each bucket.

There are some VERY clever people on this forum though so perhaps someone will come up with something that will blow my mind.

 

[jasonb75]

However, the part that makes it nearly impossible (in my mind) is there would be no way to know in the beginning how many buckets there would need to be or even how many items would be in each bucket.

I was thinking

BUCKETS=ROUNDUP(SUM(A:A),0)
ITEMS=COUNT(A:A)/BUCKETS

But that's as far as I got, in regard to the full solution, I'm adding my vote to the borderline impossible pile.

To get a result, in my mind, would require cycling through all possible permutations until everything fits. Even using vba, I expect that the sun will run out of hydrogen before it finished processing.

 

[West Man]

This is the bet I could come up with (requires sorting by tonnage first

[TABLE="width: 240"]
<colgroup><col width="64" style="width: 48pt;" span="5"> <tbody>[TR]
[TD="width: 64, bgcolor: #FAFAFA"]Tonnage[/TD]
[TD="width: 64, bgcolor: #FAFAFA"]Bucket[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]Buckets[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]5.6553[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]178[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]199[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]313[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]332[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]417.06[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]423[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]494[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]539.94[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]601[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]634.91[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]644.59[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]699.59[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]827.41[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]931.99[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]1076.3[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]1091.4[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]1376.5[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]1570[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]1729.1[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]1753.7[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]2084[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]2122[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]2153[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #FAFAFA, align: right"]2527[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


Formula for E2 =ROUNDUP(SUM(A2:A28)/10000,0)
For b2 and down =IF(ISEVEN(INT((ROWS(A$2:A2)-1)/E$2)),MOD(ROWS(A$2:A2)+E$2-1,E$2)+1,E$2-MOD(ROWS(A$2:A2)+E$2-1,E$2))