How to divide a group of numbers in to 3 groups as equal as possible.

herbeey,

Welcome to the forum!
Attached is a modified version of the workbook you posted.
First I created a dynamic named range called listNumbers which is defined with this formula:

Please Login or Register  to view this content.

Then in cells C1, D1, and E1 are the headers Group1, Group2, and Group3
In cell C2 and copied down is this formula (for the first set of numbers):

Please Login or Register  to view this content.

In cell D2 and copied down is this formula (for the second set of numbers):

Please Login or Register  to view this content.

In cell E2 and copied down is this formula (for the third set of numbers):

Please Login or Register  to view this content.

Does that work for you?

Hi,

Does the attached help.

Column C is your original list but sorted. Column D is a formula which identifies every third row and applies one of three labels. F3:G3 then summarise the totals by group.

herbeey, who are you talking to?

When i change the amount of numbers, it works just fine for me. Can you post a sample that is experiencing the problem?

Is there a reason you're not doing these groups of numbers one at a time? You can copy -> paste special -> values the results of the groups to save the results, either elsewhere in the sheet or on a different sheet. In all cases, it works just fine if you paste the number list into column A.

I have included an updated version of your workbook, but you can't see me do the intermediate steps. All I did was clear column A (selected the column and pressed Del), then copied one of the sets of numbers and pasted it into column A. The outputs of columns C:E generated the split lists just fine and I did a copy -> paste special -> values over to the right in order to save the results. Wash/rinse/repeat as necessary.

oooh, you want the group totals to be as close as possible. ok, I misunderstood your requirement. Give these three formulas a try then:

In cell C2 and copied down:

Please Login or Register  to view this content.

In cell D2 and copied down:

Please Login or Register  to view this content.

In cell E2 and copied down:

Please Login or Register  to view this content.

That will return consistent results regardless of length of data or sort. The totals for each group should be somewhat similar, though it depends on the data's outliers.

Originally Posted by herbeey

... if i change the numbers or the order i cant get it to work out evenly.

How near do the totals have to be. Are the totals
16277
18701
17132

too far apart from the overall average of 17508