Matrixes and Randomization in Excel

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.

Jam it back in, in the dark.

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.

Most amazing jew boots

Originally Posted by Araes

If you want a long list of potential matches, then the limit depends on: the length of the list, the variance of the list, and how many matches you want. More than that, I can't say, as its complex.

If you want one match, then just do a brute force.

if( tolerance > LastTotal1Diff ){
tolerance = LastTotal1Diff;
}

which will end up with the closest match for your first sum if you assign Total1Diff to LastTotal1Diff at the end of each successful loop.

You could also do minima of the total difference for both sets if they weren't going to add up to such a close set of numbers.

You can also solve this in Excel using packrat's general strategy, if you don't like coding. X columns for the bit mask, X columns for your numbers, summary columns that give the totals, columns for the differences, and a column for the difference total. In a simple form with three numbers.

Target 1	Target 2									
100000		20000									
Bit 1	Bit 2	Bit 3	30405	2103	128923	Sum 1	Sum 2	Diff 1	Diff 2	Diff Total
0	0	0	0	0	0	0	161431	100000	141431	241431
0	0	1	0	0	128923	128923	32508	28923	12508	41431
0	1	0	0	2103	0	2103	159328	97897	139328	237225
0	1	1	0	2103	128923	131026	30405	31026	10405	41431
1	0	0	30405	0	0	30405	131026	69595	111026	180621
1	0	1	30405	0	128923	159328	2103	59328	17897	77225
1	1	0	30405	2103	0	32508	128923	67492	108923	176415
1	1	1	30405	2103	128923	161431	0	61431	20000	81431

If you did that in Excel for the set sizes Zerg has you'd end up with a monster of a spreadsheet though. 25 bit binary is over 33 and a half million rows just for the matrix!

I have to do this thing quite a lot but on a much smaller scale. People inevitably pay several cheques into the bank at once but don't always provide a paying in book so you have a list of cheques received and a list of bankings and need to marry the two up, so you're looking for numbers that match a target. The advantage is I guess that you know there's a solution and, being an accountant, pretty close is generally good enough although on the occasions where you need it exact, it can be a lengthy process but the more you do it, the more you get a feel for what adds up together.

I'd still be inclined to do it manually, but then I haven't a fucking clue about coding and a 67 million row spreadsheet just seems like a bit of overkill to me. Granted it wouldn't take too long to set up but the computer I use at work would pretty much tell me to fuck off if I tried to get it to calculate that I'd have thought.

Additional Spam:
As a matter of interest though, is there any decent quick way to create a binary matrix that big in Excel? I would have said that numbers across the top and down the side then divide the side number by the top number and if it's an integer, put in a "1", if it's not put in a "0" would do it but as far as I'm aware, Excel has no "Is an integer" command. I don't see how you'd go about creating a 33 million row matrix without spending half a lifetime tapping in ones and zeros, unless of course Excel has a handy create-a-binary-matrix function I don't know about (Which is entirely possible).

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

Hahahaha.

I did initially try to work it out the same way Areas did but whilst his way is great for three or four numbers, the sheer time and effort needed to create a 25 bit binary matrix with each bit in a seperate cell is beyond prohibitive. If you had it all setup then you could just tap in the numbers from your data set and the two targets then sort the results by the size of the difference column. I know the newest version of Excel has a DEC2BIN command which converts decimals to binary but I don't know how to get that number split over several cells.

Ah, unless you use the search command. Have the numbers from 1 to 33,554,431 in column A, use DEC2BIN in column B to turn it into binary then in columns C to AA, have the numbers 1 to 25 across the top and tell Excel to report the figure in column B that is in the position listed at the top of the page. If you then copy the shit down to the bottom you've got a big old binary matrix and can then use an IF(c3=1,ab$2,0) job in the next 25 cells, copy that down and have every possible permutation listed out, then stick in your target numbers and some cells to calculate the differences, sort by the difference column and you're done!

I can't remember how to get the specific character out of a string off the top of my head though. SEARCH possibly?

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

I thought you got unlimited rows these days?

Maybe I was told that by someone who's not had to use more than a million or so...

I was speaking idiomatically.

The problem with using Solver like that is that you're only looking for a "right" answer, whereas Zerg doesn't know if there is a right answer, he just wants the closest answer. Yes, you can give it some leeway by changing the total boxes to less thans or whatever but you've still got to sit there increasing the maximum error a bit at a time to find the optimal solution, otherwise if you stick in too big of an allowable error you're going to end up with multiple solutions and not know for certain which is best.

It's possible that you could use Solver slightly differently to sort this out but I've not got my Office disk handy so I can't install the add on and play around with it.

What kind of toxic man-thing is happening now?