split into columns with balanced sums?

[jyllyd]

Hi,

I have a column (A) of values which I want to split into 3 columns.

The sum of each of the 3 columns must be as 'balanced' / near equal to each other as is possible.

e.g. if column (A) had the values:

17 17 18 17 18 18

then the 3 columns would each be:

17,18 = 35
17, 18 = 35
17, 18 = 35

(that's an oversimplified example. Column (A) will have many more values and they could be anything)

is this possible in excel & if so can anybody help?

much appreciated

[AlKey]

For this example: Enter 17 in cell B1 and 18 in C1
Enter formula in B2 and copy across and down

Please Login or Register  to view this content.

and to SUM in D2 and copy down

Please Login or Register  to view this content.

v	A	B	C	D
1	Values	17	18	SUM Values
2	17	17	18	35
3	17	17	18	35
4	18	17	18	35
5	17
6	18
7	18
8

[popipipo]

Please Login or Register  to view this content.

Or maybe these two formulas?

[Alf]

There is also the possibillity of using the Excel Add-in "solver". Not installed by default when excel is installed so you may need to add it if "Solver" not found in "Data" tab.

Due to a constraint limit set by the makers of solver the column you wish to split can not contain more than 60 - 65 numbers.

Alf