Combination, the product of two columns
- Thread starter bines53
- Start date
Jake Collins
Active Member
Dear Bines
This formula is a bit shorter for you
=A2*B2+A3*SUM(B2:B3)+A4*SUM(B2:B4)+A5*SUM(B2:B5)+A6*SUM(B2:B6)+A7*SUM(B2:B7)+A8*SUM(B2:B8)+A9*SUM(B2:B9)+A10*SUM(B2:B10)+A11*SUM(B2:B11)+A12*SUM(B2:B12)
This formula is a bit shorter for you
=A2*B2+A3*SUM(B2:B3)+A4*SUM(B2:B4)+A5*SUM(B2:B5)+A6*SUM(B2:B6)+A7*SUM(B2:B7)+A8*SUM(B2:B8)+A9*SUM(B2:B9)+A10*SUM(B2:B10)+A11*SUM(B2:B11)+A12*SUM(B2:B12)
XOR LX
Active Member
Hi again!
Either (my preference):
=SUMPRODUCT(A2:A12,MMULT(N({1;2;3;4;5;6;7;8;9;10;11}>={1,2,3,4,5,6,7,8,9,10,11}),B2:B12))
or the volatile:
=SUMPRODUCT(A2:A12,SUBTOTAL(9,OFFSET(B2,,,{1;2;3;4;5;6;7;8;9;10;11})))
If the number of rows is not fixed, and you're not sure how to generate the static arrays I give here dynamically, let me know and I'll be happy to show you.
Cheers
Either (my preference):
=SUMPRODUCT(A2:A12,MMULT(N({1;2;3;4;5;6;7;8;9;10;11}>={1,2,3,4,5,6,7,8,9,10,11}),B2:B12))
or the volatile:
=SUMPRODUCT(A2:A12,SUBTOTAL(9,OFFSET(B2,,,{1;2;3;4;5;6;7;8;9;10;11})))
If the number of rows is not fixed, and you're not sure how to generate the static arrays I give here dynamically, let me know and I'll be happy to show you.
Cheers
Hi XOR LX ,
Excellent !
I would like, with your permission, another step on the capabilities of Excel,
1)Is it possible to get a solution, the combination will not include the rows, which appears, number one, that is, without lines, 4, 7, 8?
2)Is it possible to get a solution, a combination not include the rows, which appears, in column A, the number three, and in column B, number one, that is, line 8?
Anyway, thank you for help !
Regards
David
Excellent !
I would like, with your permission, another step on the capabilities of Excel,
1)Is it possible to get a solution, the combination will not include the rows, which appears, number one, that is, without lines, 4, 7, 8?
2)Is it possible to get a solution, a combination not include the rows, which appears, in column A, the number three, and in column B, number one, that is, line 8?
Anyway, thank you for help !
Regards
David
XOR LX
Active Member
Hi David.
Not sure what you mean here.
If we exclude the 1s from column A then we will be left with an array comprising 10 elements. Yet if we exclude the 1s from column B then we will be left with an array comprising 9 elements.
Given that these arrays are now of an unequal dimension, how does this affect what you want to see as a result? What precisely is your expected result for the data you posted?
Regards
Not sure what you mean here.
If we exclude the 1s from column A then we will be left with an array comprising 10 elements. Yet if we exclude the 1s from column B then we will be left with an array comprising 9 elements.
Given that these arrays are now of an unequal dimension, how does this affect what you want to see as a result? What precisely is your expected result for the data you posted?
Regards
Hi Prasad ,
I check whether there is a relationship between stock index, and a specific stock at a level of probability.
Hi XOR LX ,
If I do exclude the 1s, I get 8 elements,
2 9
5 8
9 7
8 6
4 8
6 2
7 5
9 6
The result should be =1725
The second question, there will be 10 elements
2 9
5 8
6 1
9 7
8 6
1 6
4 8
6 2
7 5
9 6
Exclude, as appears 3s, IN column A, in parallel with 1s IN column B appeared.
The result should be =2069
Regards
David
I check whether there is a relationship between stock index, and a specific stock at a level of probability.
Hi XOR LX ,
If I do exclude the 1s, I get 8 elements,
2 9
5 8
9 7
8 6
4 8
6 2
7 5
9 6
The result should be =1725
The second question, there will be 10 elements
2 9
5 8
6 1
9 7
8 6
1 6
4 8
6 2
7 5
9 6
Exclude, as appears 3s, IN column A, in parallel with 1s IN column B appeared.
The result should be =2069
Regards
David
XOR LX
Active Member
That one would be:
=SUMPRODUCT(N(MMULT(N(A2:B12={3,1}),{1;1})<>2),A2:A12,MMULT(N(ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A2:A12)))>=COLUMN(INDEX(1:1,1):INDEX(1:1,ROWS(A2:A12)))),B2:B12*(MMULT(N(A2:B12={3,1}),{1;1})<>2)))
Put the 3 and 1 in a contiguous worksheet (row) range, if you like, e.g. D2:E2, after which the above is:
=SUMPRODUCT(N(MMULT(N(A2:B12=D2:E2),{1;1})<>2),A2:A12,MMULT(N(ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A2:A12)))>=COLUMN(INDEX(1:1,1):INDEX(1:1,ROWS(A2:A12)))),B2:B12*(MMULT(N(A2:B12=D2:E2),{1;1})<>2)))
Cheers!
=SUMPRODUCT(N(MMULT(N(A2:B12={3,1}),{1;1})<>2),A2:A12,MMULT(N(ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A2:A12)))>=COLUMN(INDEX(1:1,1):INDEX(1:1,ROWS(A2:A12)))),B2:B12*(MMULT(N(A2:B12={3,1}),{1;1})<>2)))
Put the 3 and 1 in a contiguous worksheet (row) range, if you like, e.g. D2:E2, after which the above is:
=SUMPRODUCT(N(MMULT(N(A2:B12=D2:E2),{1;1})<>2),A2:A12,MMULT(N(ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A2:A12)))>=COLUMN(INDEX(1:1,1):INDEX(1:1,ROWS(A2:A12)))),B2:B12*(MMULT(N(A2:B12=D2:E2),{1;1})<>2)))
Cheers!
Hi XOR LX ,
The answer to the second question, that's great !
I'm sorry about the first question, I did not explain correctly,
I will try, with another numerical example,
If column A, contains a 2 then exclude the whole row ,and if column B, contains a 8 then exclude the whole row,the result should be=910
Thank you !
David
The answer to the second question, that's great !
I'm sorry about the first question, I did not explain correctly,
I will try, with another numerical example,
If column A, contains a 2 then exclude the whole row ,and if column B, contains a 8 then exclude the whole row,the result should be=910
Thank you !
David
XOR LX
Active Member
Are you sure you don't mean 913?
Using D2:E2 again:
=SUMPRODUCT(N(MMULT(N(A2:B12=D2:E2),{1;1})=0),A2:A12,MMULT(N(ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A2:A12)))>=COLUMN(INDEX(1:1,1):INDEX(1:1,ROWS(A2:A12)))),B2:B12*(MMULT(N(A2:B12=D2:E2),{1;1})=0)))
Regards
Hi XOR LX ,
Your site, this issue discussed,
http://excelxor.com/2015/02/10/which-numbers-add-up-to-total-2-multiple-solutions/
I think the approach you presented here, can apply there as well.
With this part ,
=MMULT(N({1;2;3;4;5;6;7;8;9;10;11}>={1,2,3,4,5,6,7,8,9,10,11}),B2:B12)
Regards
David
Your site, this issue discussed,
http://excelxor.com/2015/02/10/which-numbers-add-up-to-total-2-multiple-solutions/
I think the approach you presented here, can apply there as well.
With this part ,
=MMULT(N({1;2;3;4;5;6;7;8;9;10;11}>={1,2,3,4,5,6,7,8,9,10,11}),B2:B12)
Regards
David
Last edited:
Hi XOR LX ,
MATCH(31,MMULT(N({1;2;3;4;5;6;7;8;9;10;11}>={1,2,3,4,5,6,7,8,9,10,11}),B2:B12),1)
Gives a result, some numbers should at least, to get a total of 31.
Second,
On your site, as there are numbers from 1 to 10, how many possible combinations to the amount of 20, the result is 8.
The question is it possible that any combination will result in one cell, that is, (5,7,8) = CB1 etc,
If you take ten numbers, each made up of a combination of three numbers, we come to 720 options,=PERMUT(10,3) or,10*9*8,
With three columns,
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 9 9
10 10 10
Included in the calculation, the unique combination, like 1,2,3 or 5,8,6
Regards
David
MATCH(31,MMULT(N({1;2;3;4;5;6;7;8;9;10;11}>={1,2,3,4,5,6,7,8,9,10,11}),B2:B12),1)
Gives a result, some numbers should at least, to get a total of 31.
Second,
On your site, as there are numbers from 1 to 10, how many possible combinations to the amount of 20, the result is 8.
The question is it possible that any combination will result in one cell, that is, (5,7,8) = CB1 etc,
If you take ten numbers, each made up of a combination of three numbers, we come to 720 options,=PERMUT(10,3) or,10*9*8,
With three columns,
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 9 9
10 10 10
Included in the calculation, the unique combination, like 1,2,3 or 5,8,6
Regards
David
Hi XOR LX ,
This formula
=IF(COLUMNS($A:A)>$L$1,"",IF(INDEX(INDEX(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),SMALL(IF(MMULT(IF(MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),Arry1^0)=$L$2,MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),0),Values)=$A$1,Arry2),COLUMNS($A:A)),),ROWS($1:1)),"X",""))
In the post,
http://excelxor.com/2015/02/10/which-numbers-add-up-to-total-2-multiple-solutions/
You give a solution, how a combination of 3 numbers gives a total of 20.
My question is, do you think it is possible, to reach the same solution, with the method you presented here in this thread .
I hope that now, my explanation is okay.
Regards
David
This formula
=IF(COLUMNS($A:A)>$L$1,"",IF(INDEX(INDEX(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),SMALL(IF(MMULT(IF(MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),Arry1^0)=$L$2,MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),0),Values)=$A$1,Arry2),COLUMNS($A:A)),),ROWS($1:1)),"X",""))
In the post,
http://excelxor.com/2015/02/10/which-numbers-add-up-to-total-2-multiple-solutions/
You give a solution, how a combination of 3 numbers gives a total of 20.
My question is, do you think it is possible, to reach the same solution, with the method you presented here in this thread .
I hope that now, my explanation is okay.
Regards
David
XOR LX
Active Member
Ah, I see now. Many thanks for your explanation.
And I see why you have made your suggestion: the techniques employed here and there are, to an extent, similar.
However, the array of 1s and 0s we are generating here with the MMULT construction is but a very small subset of the matrix produced by the formula given at that link.
Regards
And I see why you have made your suggestion: the techniques employed here and there are, to an extent, similar.
However, the array of 1s and 0s we are generating here with the MMULT construction is but a very small subset of the matrix produced by the formula given at that link.
Regards