Number balancer

[flammabubble]

Hey all,

Lets say I have the following 12 numbers:
1082
1043
1010
1002
1000
1000
995
992
985
984
980
974


I'm looking to find/build a tool within excel which will 'balance' those numbers into two groups of 6 with as close an average as possible. Does anyone have any suggestions as to how I would go about doing this?

 

[Eric W]

Here's a way to do it with formulas:

Book1
	A	B	C	D
1	1082	0	1509
2	1043	1
3	1010	0
4	1002	1
5	1000	1	0	1003.833
6	1000	1	1	1004
7	995	1
8	992	0
9	985	0
10	984	1
11	980	0
12	974	0
Sheet9

Cell Formulas
Range	Formula
C1	C1	=MATCH(MIN(IF(LEN(SUBSTITUTE(BASE(ROW(INDIRECT("1:4096"))-1,2,12),"1",""))=6,ABS(MMULT((-1)^MID(BASE(ROW(INDIRECT("1:4096"))-1,2,12),{1,2,3,4,5,6,7,8,9,10,11,12},1),A1:A12)),9^9)),IF(LEN(SUBSTITUTE(BASE(ROW(INDIRECT("1:4096"))-1,2,12),"1",""))=6,ABS(MMULT((-1)^MID(BASE(ROW(INDIRECT("1:4096"))-1,2,12),{1,2,3,4,5,6,7,8,9,10,11,12},1),A1:A12)),9^9),0)
D5:D6	D5	=AVERAGEIF($B$1:$B$12,C5,$A$1:$A$12)
B1:B12	B1	=MID(BASE($C$1-1,2,12),ROWS($B$1:$B1),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Put all the numbers marked with 0 in one team, 1 in the other. Unfortunately, I don't know much about Google Sheets, so I can't tell you if converting an Excel formula to GS is any easier than converting an Excel script to GS. The C1 formula is pretty hairy. The formulas are specific to 12 numbers, but I can adapt it if you want to an arbitrary even number (within limits).