P
Paul Black
I manually input combinations of say 3 numbers ...
Combination ( 1 ) - Cells B3
3 = Combination 01 02 03
Combination ( 2 ) - Cells B44 = Combination 01 02 04
Combination ( 3 ) - Cells B55 = Combination 01 03 05
.... in an Excel sheet named "Data" in Cells "B3?" or whatever.
In a sheet named "Statistics" in Cell "D10" I would like to have the
total combinations covered for the 2 if 3 category.
This requires calculating ALL the combinations available ( Equivalent
to Excels COMBIN(5,3) formula ) in memory and then comparing each one
to each combination in the sheet named "Data" to see if there is at
LEAST 2 numbers in common. If there is then that particular
combination is covered and 1 is added to the total and there is no
need to check the rest of the cominations for that particular 2 if 3
scenario.
All the combinatios for 3 numbers from 5 are as follows ...
( A ) 3 number = 01 02 03 produces 2 number = 01 02, 01 03, 02 03
( B ) 3 number = 01 02 04 produces 2 number = 01 02, 01 04, 02 04
( C ) 3 number = 01 02 05 produces 2 number = 01 02, 01 05, 02 05
( D ) 3 number = 02 03 04 produces 2 number = 02 03, 02 04, 03 04
( E ) 3 number = 02 03 05 produces 2 number = 02 03, 02 05, 03 05
( F ) 3 number = 03 04 05 produces 2 number = 03 04, 03 05, 04 05
.... so ...
( A ) 01 02 is covered in combination ( 1 ) so 1 is added to the total
for 2 if 3 and we can stop checking for that cover although 01 02 is
also covered in combination ( 2 ).
( A ) 01 03 is covered in combination ( 1 ) so 1 is added to the total
for 2 if 3 and we can stop checking for that cover although 01 03 is
also covered in combination ( 3 ).
( A ) 02 03 is covered in combination ( 1 ) so 1 is added to the total
for 2 if 3 and we can stop checking for that cover ( not covered
anywhere else anyway ).
( B ) 01 02 is covered in combination ( 1 & 2 ) but we ignore it
because is was covered in ( A ) and 1 has already been added to the
total.
( B ) 01 04 is covered in combination ( 2 ) so 1 is added to the total
for 2 if 3 and we can stop checking for that cover ( not covered
anywhere else anyway ).
( B ) 02 04 is covered in combination ( 2 ) so 1 is added to the total
for 2 if 3 and we can stop checking for that cover ( not covered
anywhere else anyway ).
( C ) 01 02 is covered in combination ( 1 & 2 ) but we ignore it
because is was covered in ( A ) and 1 has already been added to the
total.
( C ) 01 05 is covered in combination ( 3 ) so 1 is added to the total
for 2 if 3 and we can stop checking for that cover ( not covered
anywhere else anyway ).
( C ) 02 05 is NOT covered anywhere and so is ignored all together.
( D ) 02 03 is covered in combination ( 1 ) but we ignore it because
is was covered in ( A ) and 1 has already been added to the total.
( D ) 02 04 is covered in combination ( 2 ) but we ignore it because
is was covered in ( B ) and 1 has already been added to the total.
( D ) 03 04 is NOT covered anywhere and so is ignored all together.
( E ) 02 03 is covered in combination ( 1 ) but we ignore it because
is was covered in ( A ) and 1 has already been added to the total.
( E ) 02 05 is NOT covered anywhere and so is ignored all together.
( E ) 03 05 is covered in combination ( 3 ) so 1 is added to the total
for 2 if 3 and we can stop checking for that cover ( not covered
anywhere else anyway ).
( F ) 03 04 is NOT covered anywhere and so is ignored all together.
( F ) 03 05 is covered in combination ( 3 ) but we ignore it because
is was covered in ( E ) and 1 has already been added to the total.
( F ) 04 05 is NOT covered anywhere and so is ignored all together.
.... which means that out of the 10 combinations tested, 7 are covered
for the 2 if 3 category.
It would be useful to have the total combinations tested which could
go in Cell "C10".
Hope this Helps.
All the Best.
Paul
Combination ( 1 ) - Cells B3
3 = Combination 01 02 03Combination ( 2 ) - Cells B4
4 = Combination 01 02 04Combination ( 3 ) - Cells B5
5 = Combination 01 03 05.... in an Excel sheet named "Data" in Cells "B3
?" or whatever.In a sheet named "Statistics" in Cell "D10" I would like to have the
total combinations covered for the 2 if 3 category.
This requires calculating ALL the combinations available ( Equivalent
to Excels COMBIN(5,3) formula ) in memory and then comparing each one
to each combination in the sheet named "Data" to see if there is at
LEAST 2 numbers in common. If there is then that particular
combination is covered and 1 is added to the total and there is no
need to check the rest of the cominations for that particular 2 if 3
scenario.
All the combinatios for 3 numbers from 5 are as follows ...
( A ) 3 number = 01 02 03 produces 2 number = 01 02, 01 03, 02 03
( B ) 3 number = 01 02 04 produces 2 number = 01 02, 01 04, 02 04
( C ) 3 number = 01 02 05 produces 2 number = 01 02, 01 05, 02 05
( D ) 3 number = 02 03 04 produces 2 number = 02 03, 02 04, 03 04
( E ) 3 number = 02 03 05 produces 2 number = 02 03, 02 05, 03 05
( F ) 3 number = 03 04 05 produces 2 number = 03 04, 03 05, 04 05
.... so ...
( A ) 01 02 is covered in combination ( 1 ) so 1 is added to the total
for 2 if 3 and we can stop checking for that cover although 01 02 is
also covered in combination ( 2 ).
( A ) 01 03 is covered in combination ( 1 ) so 1 is added to the total
for 2 if 3 and we can stop checking for that cover although 01 03 is
also covered in combination ( 3 ).
( A ) 02 03 is covered in combination ( 1 ) so 1 is added to the total
for 2 if 3 and we can stop checking for that cover ( not covered
anywhere else anyway ).
( B ) 01 02 is covered in combination ( 1 & 2 ) but we ignore it
because is was covered in ( A ) and 1 has already been added to the
total.
( B ) 01 04 is covered in combination ( 2 ) so 1 is added to the total
for 2 if 3 and we can stop checking for that cover ( not covered
anywhere else anyway ).
( B ) 02 04 is covered in combination ( 2 ) so 1 is added to the total
for 2 if 3 and we can stop checking for that cover ( not covered
anywhere else anyway ).
( C ) 01 02 is covered in combination ( 1 & 2 ) but we ignore it
because is was covered in ( A ) and 1 has already been added to the
total.
( C ) 01 05 is covered in combination ( 3 ) so 1 is added to the total
for 2 if 3 and we can stop checking for that cover ( not covered
anywhere else anyway ).
( C ) 02 05 is NOT covered anywhere and so is ignored all together.
( D ) 02 03 is covered in combination ( 1 ) but we ignore it because
is was covered in ( A ) and 1 has already been added to the total.
( D ) 02 04 is covered in combination ( 2 ) but we ignore it because
is was covered in ( B ) and 1 has already been added to the total.
( D ) 03 04 is NOT covered anywhere and so is ignored all together.
( E ) 02 03 is covered in combination ( 1 ) but we ignore it because
is was covered in ( A ) and 1 has already been added to the total.
( E ) 02 05 is NOT covered anywhere and so is ignored all together.
( E ) 03 05 is covered in combination ( 3 ) so 1 is added to the total
for 2 if 3 and we can stop checking for that cover ( not covered
anywhere else anyway ).
( F ) 03 04 is NOT covered anywhere and so is ignored all together.
( F ) 03 05 is covered in combination ( 3 ) but we ignore it because
is was covered in ( E ) and 1 has already been added to the total.
( F ) 04 05 is NOT covered anywhere and so is ignored all together.
.... which means that out of the 10 combinations tested, 7 are covered
for the 2 if 3 category.
It would be useful to have the total combinations tested which could
go in Cell "C10".
Hope this Helps.
All the Best.
Paul