Cell groups mAh difference sorting - any maths/excel/formula

[smeagol222]

I have 130 cells with capacity numbers between 1000 and 3000 mAh. Preferrably this to be done in excel

They are in 13 columns, 10 rows each (13 groups of 10 numbers)

I want to sort the numbers so they are in groups with the least amount of variance between the largest group total and the smallest group total.

I've attached an excel file to show what I mean- want to make the "difference" number as small as possible.

 

[cycborg]

To do this in Excel:

Put all the numbers in the first column of a blank sheet (A1:A130).
Sort that column in descending order.
Move the bottom half of the cells (A66:A130) into the second column, next to the top half (B1:B65).
Sort this second column (only!) in ascending order.
Move the cells in A14:B26 into C113
Move the cells in A27:B39 into E1:F13
Do this two more times with the remaining cells in A:B.

Now your cell groups are in rows - sum across the rows to check. Here's what I get with your numbers (last column are totals):

2988 1018 2734 1190 2540 1262 2365 1493 2137 1721 19448
2983 1018 2729 1195 2518 1268 2362 1500 2070 1726 19369
2979 1022 2710 1196 2516 1269 2343 1507 2060 1741 19343
2977 1040 2696 1202 2512 1278 2336 1524 2049 1759 19373
2968 1062 2692 1208 2485 1357 2324 1538 2035 1796 19465
2936 1072 2639 1210 2483 1360 2298 1544 2032 1813 19387
2925 1073 2625 1223 2471 1386 2295 1553 2020 1814 19385
2917 1130 2624 1230 2445 1397 2275 1565 2018 1850 19451
2882 1147 2617 1234 2412 1419 2254 1579 2001 1856 19401
2859 1160 2596 1239 2386 1424 2233 1624 1967 1859 19347
2851 1160 2571 1251 2370 1460 2231 1665 1959 1873 19391
2777 1168 2554 1254 2368 1478 2210 1669 1951 1900 19329
2744 1178 2549 1256 2366 1488 2164 1672 1935 1906 19258

 

[smeagol222]

AMAZING! Got a diff of only 207mAh
Is this a sure way to find best possible grouping? and is this the process you use to group cells when building a battery yourself?

 

[cycborg]

It's closer to "quick and dirty" than "best possible". With a large number of cells and a fairly uniform distribution of capacities, it works pretty well; in this case the groups are within 1%. With a smaller number of cells and/or a less uniform distribution, you could use this as a starting point, and then swap cells here and there to get closer. For example, here you could swap E5 and E13 to narrow the range to 122 mAh. I chose these because they come from the groups with the largest and smallest capacity, and the cells' difference is roughly equal to the groups' variation from the average.

Never built a battery myself; I'm basically approaching it as a logic puzzle. :wink:

 

[smeagol222]

Managed to get it down even further by swapping cells that had close numbers from taking from highest group, giving to lowest group. I think 40mAh is pretty good! Each time I did this the highest group and lowest group would change, so then I would switch to those groups and repeat. I think from the sorting at the start the cells were usually above or below each other. I got to 40mAh diff. when I exhausted all cell swapping (diffs got higher than 40mAh)

 

[smeagol222]

Now all I need is an Excel export to help me figure out a macro to do the above for different sized groups. Input number of groups (voltage) group size (capacity), then it can auto format one column with all numbers. Maybe even automate the manual process at the end!

eg. 48v battery is 13 groups, 72v battery would be 20 groups

 

[knutek]

I used this page and calculated your pack: https://secondlifestorage.com/repacker.php

Batteri 1 2979 2917 2925 2617 2988 2734 2516 2624 2936 2696 2983 2859 2977
Batteri 2 2968 2625 2744 2471 2512 2710 2445 2483 2692 2596 2729 2851 2777
Batteri 3 2882 2549 2639 2343 2370 2485 2368 2412 2164 2540 2571 2366 2554
Batteri 4 2018 2518 2386 2049 2231 2233 2295 2362 1873 2324 2298 2336 2070
Batteri 5 1856 2275 2365 2032 1959 2210 2060 2137 1796 1951 1850 2254 2035
Batteri 6 1624 1813 1357 2001 1721 1900 1967 1935 1741 1538 1579 2020 1906
Batteri 7 1507 1234 1269 1759 1544 1478 1859 1672 1726 1493 1488 1278 1814
Batteri 8 1256 1230 1268 1553 1500 1262 1669 1524 1665 1460 1419 1202 1168
Batteri 9 1251 1147 1239 1424 1360 1208 1178 1160 1565 1397 1254 1195 1062
Batteri 10 1040 1073 1190 1130 1196 1160 1022 1072 1223 1386 1210 1018 1018
Total cap 19381 19381 19382 19379 19381 19380 19379 19381 19381 19381 19381 19379 19381
cap under max -1 -1 0 -3 -1 -2 -3 -1 -1 -1 -1 -3 -1

Capasity of battery 19379 mAh