Matrixes and Randomization in Excel

Well, I'm not entirely sure what to call the topic, but it's simpler to explain what I am trying to do.

I have an array of numbers, the list of which is:

1. 508791
2. 110776
3. 60642
4. 1268384
5. 1831963
6. 5141077
7. 2200967
8. 1079081
9. 590535
10. 2755461
11. 176599
12. 16323547
13. 1739899
14. 452583
15. 6730411
16. 5657172
17. 9865374
18. 561737
19. 36144212
20. 22983457
21. 6164492
22. 8489925
23. 10450763
24. 812882
25. 22147053

They add up to 164,247,783

I am trying to group them in such a way that the first group is close to 94,204,280 and the second group is close to 70,043,499. These do not add up exactly, but are 4 figures off (164,247,789).

It's going to take me a lot of manual combinations to do this properly, even if it's at all possible.

So, is there a way to get Excel to do this automagically? I have already tried Solver and Goal Seek, but am unable to come up with a viable solution.

Jam it back in, in the dark.

I don't know how powerful Excel scripting is. I know you can run Visual Basic scripts on it, but I don't know how to program. So admittedly when you talk about algorithm methods like "genetic" or "quasi-Newton gradual descent", I'm afraid I'm totally clueless.

Originally Posted by Mo0

*psst* Zerg

That means a computer will be really terrible at this

It's probably going to take the computer a ton of time to do, but hey. It's going to take me much more time to do. So, I don't really care about that

Originally Posted by Shin

Jesus. Variable group sizes and not having to group them in order means even if you did stick them all in some kind of matrix, it'd be fucking huge with all the permutations. I honestly think it'd be quicker to do with trial and error than to calculate it.

You were my best hope, Excel buddy ;___;

There's nowhere I can't reach.

Wow, you nailed it exactly!

I think I get the gist of your logic, incrementing a binary matrix that is as long as my set, performing a sumtotal, and getting the difference. How do you come up with the limit variable? Seems to be 2 ^ [size of matrix] - 1?

Originally Posted by Shin

15 minutes of trial and error tells me that:

110,776.00
1,079,081.00
2,755,461.00
8,489,925.00
9,865,374.00
10,450,763.00
16,323,547.00
22,147,053.00
22,983,457.00

Add up to 94,205,437, 1,157 more than one of your target numbers. The way I see it is this. You must have one or both of the largest numbers in the bigger group as without them, the rest don't add up to enough to form the bigger group. I then took groups of numbers that added up to just under the 94,204,280 target, and tried to make the difference using what was left. By changing one thing at a time you can zero in on the amount pretty quickly.

I'm not saying they can't be divided closer to your target but for 15 minutes work, it's pretty fucking close.

Appreciate the time you put into this, mate. However, this was one of many matrices I was hoping to match, so I pretty much had to look for a computational alternative or lose my sanity

This thing is sticky, and I don't like it. I don't appreciate it.

So... no? Or yes?

I am a dolphin, do you want me on your body?

My first attempt was actually to set up an Excel worksheet in this manner:

Column A is the "Bits" column.
Column B has the matrix
Column C outputs Column B if the row in Column A is 1, and zero if it's not.
Column D outputs Column B if the row in Column A is 0, and zero if it's not.
I summed up column C and column D, and deduct the totals by the desired figures. I then added the absolute values of both, and placed it in E1

Then I entered Solver, and said:

Set E1 to Minimum by Changing Column A cells
Subject to the following constraints:
- Column A cells are integers
- Column A cells are greater than or equal to zero
- Column A cells are lesser than or equal to one

I then set 9999 iterations.

Seeing as how brute-forcing this took packrat 8 million iterations, I guess I shouldn't be so surprised that Excel refused to do this

I was speaking idiomatically.

Araes' method is impossible to do in Excel 2007, period. For the simple reason that Excel only supports 1.4+ million rows

Most amazing jew boots

I'm bumping this to say that I've found a nice little Excel add-in called "Find Combinations"

It purports to be especially useful for auditors dealing with clients who are especially... sloppy when it comes to recording invoice numbers. As it is with my problem, you have a total expense figure that you're trying to look for. You also have a set of numbers you're trying to combine in various ways to reach that total.

The difference is that it looks for exact summation, while my figures are not precise. While this is still unsuitable for the purposes of this thread, but may be of use to someone else.

While installing, you will need to add this site to your "Trusted Sites" in Internet Explorer:
http://s3downloads.lyquidity.com

P.S.: My exercise was basically an attempt to brute-force how Michael Porter classifies data from the UN ComTrade database to his nice little "Porter Cluster Model." He was quite reluctant to share it with us, hence the brute force. Haven't been able to get satisfactory results across years, so I eventually abandoned the effort.

FELIPE NO