# Any Excel experts out there? Battery grouping Question.



## rwaudio (May 22, 2008)

I'm measuring the capacity of all of my cells so that I can group them in a way that the total capacity of each group is the same for all groups. The manual way is take the high cell, low cell then a cell from the middle to equal the measured average cell x3. (I'm building a 90 or 96S3P pack of A123 20Ah pouches)

IE. if the average of my cells was 18.5Ah I would want to make groups that are all as close to 55.5Ah as possible. (All of my cells so far measure in the 0.6 to 0.7milliohm range using a PL6, anything very far from this will not be used in the main pack regardless of capacity) 

The problem is there aren't going to be cells of the correct capacity for this to work out all the time. And as I use up the cells I will be further and further above or below the ideal value.

I'm wondering if there is a way to use Excel (or something else) to group the cells in the most efficient way for the closest average groupings for the target value (55.5Ah if that's what the final number turns out to be).

Any help would be great!


----------



## Ziggythewiz (May 16, 2010)

Not sure what you're trying to automate. The Excel version is the same as your manual, sort all the values, delete the out of spec ones, and group the middle one with the ones on each end. Then take the next closest to the middle and grab the new ends.

It comes out the opposite of what you said though, the last ones will be closest. This assumes that the middle 1/3 is virtually identical, which as I've heard is typical for lithium.

I don't think anyone can get Excel to read the numbers and physically group the pouches for you though, so not sure what you want in a non-manual method.


----------



## rwaudio (May 22, 2008)

Ziggythewiz said:


> Not sure what you're trying to automate. The Excel version is the same as your manual, sort all the values, delete the out of spec ones, and group the middle one with the ones on each end. Then take the next closest to the middle and grab the new ends.
> 
> It comes out the opposite of what you said though, the last ones will be closest. This assumes that the middle 1/3 is virtually identical, which as I've heard is typical for lithium.
> 
> I don't think anyone can get Excel to read the numbers and physically group the pouches for you though, so not sure what you want in a non-manual method.


As you say the manual way is the same as what you mentioned and not what I'm looking for.

What you mention in the last line is what I'm looking for. I don't know how to do it, but I do know that Excel and or scripts for Excel can be very powerful. Just wondering if someone knows how to do it that way.


----------



## Ziggythewiz (May 16, 2010)

rwaudio said:


> What you mention in the last line is what I'm looking for. I don't know how to do it, but I do know that Excel and or scripts for Excel can be very powerful. Just wondering if someone knows how to do it that way.


When I say physically, I mean reach out and pick up a pouch and stick it in a pile. It sounds like you want a complicated method to simplify the easiest part of your task.

Do you already have data in a file that I can throw code at?


----------



## rwaudio (May 22, 2008)

Ziggythewiz said:


> When I say physically, I mean reach out and pick up a pouch and stick it in a pile. It sounds like you want a complicated method to simplify the easiest part of your task.
> 
> Do you already have data in a file that I can throw code at?


Well I don't mean physically, but I mean in a way that groups the cells together so that every group of 3 is the same total value. The method you mention (which is virtually the same as the manual method I mention) does not do this.

Here is a list of my "defective" cells, the ones well below capacity. I'm using these for a 12v battery (4S3P).

15.77
16.07
16.32
16.43
16.44
16.51
17.05
17.33
17.45
17.49
17.73
17.75


The average value is 16.86 so I would like 4 groups that are each 50.58Ah (well as close to it as possible)
You will see that without the ideal Gaussian distribution it's hard to make it work using the manual method.

This is simply an example and I'm not that concerned about the 12v battery, it will be top balanced and should never get discharged as far as the main pack. 

Here are a couple dozen cells I have rechecked (switched to a PL6, so I'm retesting 81 cells....) This is on the way to a total of 270 or 288.

18.26
18.33
18.40
18.44
18.49
18.50
18.52
18.55
18.56
18.59
18.59
18.60
18.64
18.70
18.70
18.75
18.78
18.81
18.81
18.83
18.84
18.88
18.90
18.93


----------



## Ziggythewiz (May 16, 2010)

Is this what you have in mind?

Change .zip to .xlsx


----------



## rwaudio (May 22, 2008)

Ziggythewiz said:


> Is this what you have in mind?
> 
> Change .zip to .xlsx


Initially no, but given the difference of only 0.25Ah it is probably completely fine if the final groupings were like this.

I can do it myself and group to within 0.03Ah, but it takes a bit of time (and some trial and error) and that's with only 24 cells, not sure how it would work with 270+

Basically what I was going for was a pack so close in capacity that makes no difference if it's top or bottom balanced, they would behave pretty much the same at both ends.


----------



## Ziggythewiz (May 16, 2010)

Maybe you could just post the full data set on a Sodoku forum and tell them each set of 3 must add up to 18.5


----------



## drgrieve (Apr 14, 2011)

I wrote a quick program that is not very smart - basically just automates a simple picking process. Can get to .075 with the sample set provided.

It's in C#, you might be able to convert it to excel macros - I haven't used them before ....

To run you'll need the .Net framework installed if you are running old version of Windows.


----------



## Ziggythewiz (May 16, 2010)

I'm too rusty on my CS to try it, but I think you could group the first third with the inverse of the last third, then try every possible combination of the middle third to get the best fit.


----------



## rwaudio (May 22, 2008)

drgrieve said:


> I wrote a quick program that is not very smart - basically just automates a simple picking process. Can get to .075 with the sample set provided.
> 
> It's in C#, you might be able to convert it to excel macros - I haven't used them before ....
> 
> To run you'll need the .Net framework installed if you are running old version of Windows.


That's pretty cool, and basically what I'm looking for.
I'll see how it deals with larger lists of numbers, seems to beat the "manual method"

Thank you!!


----------



## rwaudio (May 22, 2008)

Ziggythewiz said:


> *Maybe you could just post the full data set on a Sodoku forum and tell them each set of 3 must add up to 18.5*


This is probably the best answer though!!


----------



## Ziggythewiz (May 16, 2010)

Do you have the rest of the data gathered?

I need to clean it up, but this is giving better results than i was getting manually. I don't know if it's better than drgrieve's result. .net doesn't like me.

Change .zip to .htm

You don't really want to minimize the total variance, but the max variance, so that's implemented using the partial brute force method I mentioned earlier, giving .018 for set 2.


----------



## drgrieve (Apr 14, 2011)

Ziggythewiz said:


> You don't really want to minimize the total variance, but the max variance


Yes this is right. Also you are really trying to reduce the minimum cell ah - as the smallest cell is what limits the pack.

Currently my min group is 55.87 with the average being 55.93 so that is 0.06 ah below average and high group is 55.98 which is 0.05 above average. Total spread 0.11 ah.

I couldn't understand your results but 0.018 seems low, do you mean 0.18?

I have an idea for a better routine, currently I'm doing only 1 pass. Going to do a N pass with aim of high as possible min cell.


----------



## drgrieve (Apr 14, 2011)

Not much improvement. Improved min group to 55.89 by 0.02, max group still 55.98. Max variation drops to 0.05, total variance 0.09.

To improve would need to move away from matching end pairs - which makes it a lot more complex. Maybe initial random pairing then trade cells until no more improvements.

(Note second calc button doesn't need a starting tolerance entered.)


----------



## Ziggythewiz (May 16, 2010)

drgrieve said:


> Yes this is right. Also you are really trying to reduce the minimum cell ah - as the smallest cell is what limits the pack.
> 
> I couldn't understand your results but 0.018 seems low, do you mean 0.18?


The smallest cell limits the pack while driving, but the highest cell limits the pack while charging, which also limits the pack while driving.

Yeah, like I said it needs to be cleaned up, but .018 is the largest group variance. It's trying every possible combination for the middle cells, while assuming inverse matching is the best approach for the high and low cells.

It would be trivial to change the code to find the absolute ideal, but it would go from testing 40k possibilities to 6E23. As it is, the real data set is ~300 instead of 24.


----------



## Ziggythewiz (May 16, 2010)

Grieve,

Finally have .net working, but don't see the second calc button. Did you post the right version?


----------



## rwaudio (May 22, 2008)

Ziggythewiz said:


> Do you have the rest of the data gathered?
> 
> I need to clean it up, but this is giving better results than i was getting manually. I don't know if it's better than drgrieve's result. .net doesn't like me.
> 
> ...


That's pretty cool too, the "total" column didn't populate for me though so I haven't directly compared totals to drgrieve's program.


18.26
18.33
18.34
18.40
18.44
18.49
18.50
18.52
18.55
18.56
18.56
18.56
18.59
18.59
18.60
18.61
18.63
18.64
18.65
18.67
18.70
18.70
18.73
18.75
18.78
18.79
18.81
18.81
18.83
18.84
18.88
18.90
18.93

Here is more of the data, 33 cells which gives 11 groups.
I have manually grouped the data into what I think is the best possible groups... can one of you match it in software?? (this did take a few minutes manually and would get very time consuming with the larger data set)

18.93	18.33	18.64	55.90
18.90	18.49	18.52	55.91
18.83	18.34	18.73	55.90
18.88	18.40	18.63	55.91
18.84	18.26	18.81	55.91
18.81	18.50	18.59	55.90
18.79	18.44	18.67	55.90
18.70	18.59	18.61	55.90
18.70	18.56	18.65	55.91
18.78	18.56	18.56	55.90
18.75	18.60	18.55	55.90

Thanks!

to add... my method was to take the average (to 3 decimal places) 55.904 which tells me that my target is 55.90 but can't avoid one or more that are higher than 55.90, so in grouping if I can't get 55.90 I try for 55.91. This got me there for 8 of 11 groups and I had to do some trades at the end to get all the groups in the desired range.


----------



## Ziggythewiz (May 16, 2010)

Total column wasn't supposed to work, that was used earlier for troubleshooting.

Your method shows a max variance of .006

Apparently my page chokes on the new data as it gives a .15


----------



## drgrieve (Apr 14, 2011)

Looks like my first approach works better for this data set. I get min of 55.88 and max of 55.93

I updated the zip file above, I forgot to copy the compiled exe from the debug subdirectory in the zip file to the top level.

It would seem that the more the data the better the results.


----------



## Ziggythewiz (May 16, 2010)

Guess mine was working, just hit the infinite loop warning sooner than i expected.

I'm getting .016 as the max variance with groups 55.89-55.92


----------



## Doctorbass (Dec 12, 2008)

I am working on a softwaree that will also match IR of the cells in addition to the capacity... 

Should be released for this summer.

I already had it working ( made with mathlab) and i got pretty close results from cell to cell

Doc


----------

