Excel Formula Problem

[bigbadmarty]

Hi there wondering if any of you could help !
I've made up a spreadsheet that gives the capacity of 65 batteries that I've revived to make a power pack. They range from 1428 to 2223mA with a mean value of 1879mA.
I need to sort them into 5 groups of 13 with approximately the same average capacity.

I think there may be a way to do this on a spreadsheet but I'm getting nowhere fast.

Cheers

[fiction-factory]

There may be a way but i think it'd be easier and quicker just to put them into 5 groups of 13, work out the mean of each group, and then swap a few around until you get a close enough match- it should only need a few swaps

[fiction-factory]

Paste all 65 values into here and I'll quickly do it for you if you want

[bigbadmarty]

Thanks it probably would but I was hoping to find a formula that could be used for larger/smaller groups if I needed to adjust the configuration of the power pack which consists of cells arranged in series and parallel.
I thought it might be easier to manipulate the values on a spreadsheet.

[bigbadmarty]

oh I didnt see you last post...
back soon with the values

[fiction-factory]

You could sort into order from smallest to largest and then make up group 1 by picking the first item and every fifth one, group 2 by picking the second plus every fifth one, etc

[fiction-factory]

Ignore that- you'd really need to use first then last, second then next to last, and so on

[bigbadmarty]

1428 1428
1436 1436
1466 1466
1493 1493
1503 1503
1527 1527
1532 1532
1543 1543
1570 1570
1574 1574
1580 1580
1593 1593
1614 1614
1649 1649
1652 1652
1694 1694
1730 1730
1804 1804
1804 1804
1813 1813
1817 1817
1818 1818
1826 1826
1826 1826
1832 1832
1848 1848
1861 1861
1861 1861
1866 1866
1885 1885
1897 1897
1901 1901
1931 1931
1960 1960
1962 1962
1992 1992
1993 1993
1998 1998
2004 2004
2016 2016
2016 2016
2018 2018
2023 2023
2038 2038
2038 2038
2044 2044
2045 2045
2050 2050
2051 2051
2056 2056
2082 2082
2083 2083
2083 2083
2086 2086
2094 2094
2098 2098
2100 2100
2102 2102
2102 2102
2110 2110
2113 2113
2118 2118
2131 2131
2150 2150
2223 2223
1879.276923 1879

[bigbadmarty]

Those are the values I've somehow duplicated them

[fiction-factory]

Sorry- I haven't worked out yet how to paste the list in that form into excel

[bigbadmarty]

try this
https://docs.google.com/spreadsheets/d/15JJvbl4hErfsKEApXsiak6ruf0TDTFf5l-0Kds9lz38/edit?ts=57c30456#gid=0

[fiction-factory]

First attempt- i put the figures into 13 columns of 5 and then alternately increasing and decreasing and then shuffled a few about
1428 1527 1580 1813 1817 1885 1897 2004 2016 2056 2082 2110 2223 1879.8
1436 1532 1593 1804 1818 1866 1962 2016 2038 2051 2083 2100 2150 1880.7
1466 1543 1614 1804 1826 1861 1931 1998 2023 2050 2083 2102 2131 1879.4
1493 1570 1649 1730 1826 1861 1960 1993 2018 2045 2086 2102 2118 1880.8
1503 1574 1652 1694 1832 1848 1962 1992 2038 2044 2094 2098 2113 1880.3

[fiction-factory]

Final figure for each of the 5 sets is the mean of the 13 figures in each of the 5 rows

[bigbadmarty]

That's pretty good ff did you use a formula ?

[fiction-factory]

No-- I just worked on the basis that as your dated was sorted highest to lowest i needed to split it up into rows and columns so as to give each row a mix of low and high data. I then alternated the sort order of the columns. I then took the mean of each row. It's pretty quick to do manually for any set of data.
I'm sure there is a way of automating the whole thing but unless I had to do loads of these I would just do it manually as I have done here

[bigbadmarty]

Ok, thanks for your help nevertheless .

With any luck this configuration will provide the power for my electric bike !

[jim360]

I think the problem is quite computationally complex, owing to the fact that there are around a million billion billion billion billion different ways of splitting your 65 batteries into five groups of 13. It's not too difficult to set up a program that would split the batteries in every possible way, compute the average of each group, and then select the grouping with the smallest range. The problem is that the number of computations just grows stupidly fast, doubling every time you add one more battery. It takes my (admittedly not superfast) laptop about ten seconds to find all subsets of 13 elements in a list of 28, twice as long in a group of 29; memory's probably an issue as well (as it's still trying to finish the N=30 task, and gave up)... another 25 doublings on this computer appears to suggest that it would take me...45,000 years or so?

Hmm. Probably better to take a guess at a likely grouping, solve by hand, perform a couple of swaps, and give up when it's close enough.

[jim360]

If it's any comfort to you, though, you could give the problem to the world's fastest supercomputer and it'd probably get back to you with the answer in something like three weeks.

[bigbadmarty]

Crikey jim I didn't realise that !
Looks like ff's practical approach is the way to go .
Thanks for the input.