List all combinations in Excel

[charlesdwright]

Chas,
Just found a bug. Not sure why I didn't see it earlier.
When you start the 5Y vs 7Y combo, the first loops run correctly but the second loop for the Y-coordinates should reset from beginning.

0.00%   3.00%   0.00%   3.00%   5Y   7y 
0.00%   3.00%   3.00%   7.00%   5Y   7y       
0.00%   3.00%   7.00%   10.00%   5Y   7y       
0.00%   3.00%   10.00%   15.00%   5Y   7y       
0.00%   3.00%   15.00%   30.00%   5Y   7y       
3.00%   7.00%   3.00%   7.00%   5Y   7y
...

The last line should be
3.00% 7.00% 0.00% 3.00% 5Y 7y

With this, the number of total combination should be (3n^2+3n(n-1)/2)

I agree.

I had to go back over my notes. Let's say (s) is the gross number of term elements in each series (31). Because they are strictly sequential, there are (s-1) elegible term elements.

If we say that (n = s-1), then:

in the "special case," where a pair consists of two elegible terms from the same series, there are ((n^2-n)/2) valid pairs.

in the cross-term case, where all pairs are elegible, there are (n^2) valid pairs.

So, all together, since we have 3 series, we have a total of (3(n^2-n)/2+3n^2) total pairs.

Just as a matter of preference, I reduced it to (3/2n(3n-1)).

Since the criteria have changed, I decided to rewrite the algo for total valid pairs. In doing so, I found an error in my 'general case' statement. It was accurate for 3 series, but not for n series.

I attempted to correct it, and its attached. If you happen to look it over and see any errors, by all means, please let me know.

 

[charlesdwright]

chazCombine 2.0

Andy,

'==================================================================================
Test this when you have some time. I've added comments to highlight the code changes. This is the version that you haven't changed the output format on, so you'll probably just grab the code changes and paste them in to your version -- it's very simple. The comments are surrounded by "===" just like this paragraph.
'==================================================================================

Once again I took the precaution of dimensioning the valid pairs array with the theoretical count algo (as posted earlier), and once again the actual count matched.

I did rush through this with a sinus headache so I'll apologize in advance for anything bizarre.

~cdw

 

[charlesdwright]

Andy:

You might want to run it as is, just to test it, in case there's an inadvertent error in the process of carving it up and adding the changes to your version.

Recall this is the one where you select the entire range, including the header.

 

[Andy Nguyen]

Chas,
Couldn't get to it until now. Took me 10m to step through your new code and mine to see where I need to adapt. The only 2 places I need to change is the function to compute unique pair and the condition at the top of the loop. After that, she is all apples, mates!
I owe you a drink, Chas.

 

[charlesdwright]

My pleasure, Andy.


I think the call to the unique pair function changed slightly, too -- if you haven't, you should have a look at that.
~c