Combination, the product of two columns
- Thread starter bines53
- Start date
Hi XOR LX ,
I'll start from the end of the idea,
The first column, there is an array formula, the first 100 lines will give the number 1, 100 following lines give the number 2, so on, the last hundred lines give the number to 10, total 1,000 lines.
The second column, there is an array formula, the first 10 rows give the number 1, 10 following lines give the number 2, so on, until number 10, so the cycle, up to 1,000 line.
The third column, there is an array formula, the first 10 rows of numbers from 1 to 10 1,2,3,4,5,6,7,8,9,10, appear to cycle, up to 1,000.
David
I'll start from the end of the idea,
The first column, there is an array formula, the first 100 lines will give the number 1, 100 following lines give the number 2, so on, the last hundred lines give the number to 10, total 1,000 lines.
The second column, there is an array formula, the first 10 rows give the number 1, 10 following lines give the number 2, so on, until number 10, so the cycle, up to 1,000 line.
The third column, there is an array formula, the first 10 rows of numbers from 1 to 10 1,2,3,4,5,6,7,8,9,10, appear to cycle, up to 1,000.
David
XOR LX
Active Member
Why do these lists need to be generated dynamically? Why does it have to be an array formula?
The first of your lists can be given simply by:
=MATCH(ROWS($1:1),100*{0,1,2,3,4,5,6,7,8,9,10}+1)
and copied down a further 999 rows.
Still not sure I'm understanding you here!
Regards
The first of your lists can be given simply by:
=MATCH(ROWS($1:1),100*{0,1,2,3,4,5,6,7,8,9,10}+1)
and copied down a further 999 rows.
Still not sure I'm understanding you here!
Regards
Hi XOR LX ,
In the first column, as in two other columns,
Formula without having to copy down, but a formula that you can get information, what value is a certain line, for example
If I want to know, what is the line 55 I will get the result 1
If I want to know, what is the line 120 I will get the result 2
If I want to know, what is the line 580 I will get the result 6
If I want to know, what is the line 862 I will get the result 9
I hope so, I explained correctly.
Regards
In the first column, as in two other columns,
Formula without having to copy down, but a formula that you can get information, what value is a certain line, for example
If I want to know, what is the line 55 I will get the result 1
If I want to know, what is the line 120 I will get the result 2
If I want to know, what is the line 580 I will get the result 6
If I want to know, what is the line 862 I will get the result 9
I hope so, I explained correctly.
Regards
Hi XOR LX ,
I'll explain what I'm trying to do,
How do I integrate the Two formulas, a summary of the lines where the line is equal to 20, provided the unique values in a row is 3
The answer should be 48
=SUM(--(MMULT(A11:C1010,{1;1;1})=20))
=SUM(--(FREQUENCY(A11:C1010,A11:C1010)>0))
Regards
David
I'll explain what I'm trying to do,
How do I integrate the Two formulas, a summary of the lines where the line is equal to 20, provided the unique values in a row is 3
The answer should be 48
=SUM(--(MMULT(A11:C1010,{1;1;1})=20))
=SUM(--(FREQUENCY(A11:C1010,A11:C1010)>0))
Regards
David
Last edited:
Hi XOR LX ,
Tomorrow morning I go on vacation, for 5 days, will not be available at the computer.
I will explain my idea ,
When you have 10 numbers, and you want to find a certain amount with 3 numbers out of 10.Prompted 720 lines, each line has 3 different numbers.
Mathematics ,10*9*8 or =PERMUT(10,3)
I attached the previous table,permutations with Repetition 10^3,
This table should be removed, 280 lines, this same lines where a certain number appears more than once.
The idea that the three columns, I made manually, you have to find that formula, and we were able to find the integrate the Two formulas #36,The answer you get is 48.
The 48 combinations are worth 20 I attach a file, with 720 rows, table that was manually.
Of course you have to polish, the same combination no more than once.
Regards
David
Tomorrow morning I go on vacation, for 5 days, will not be available at the computer.
I will explain my idea ,
When you have 10 numbers, and you want to find a certain amount with 3 numbers out of 10.Prompted 720 lines, each line has 3 different numbers.
Mathematics ,10*9*8 or =PERMUT(10,3)
I attached the previous table,permutations with Repetition 10^3,
This table should be removed, 280 lines, this same lines where a certain number appears more than once.
The idea that the three columns, I made manually, you have to find that formula, and we were able to find the integrate the Two formulas #36,The answer you get is 48.
The 48 combinations are worth 20 I attach a file, with 720 rows, table that was manually.
Of course you have to polish, the same combination no more than once.
Regards
David
XOR LX
Active Member
So have you tried the version of the solution at the very foot of that post I made:
http://excelxor.com/2015/02/10/which-numbers-add-up-to-total-2-multiple-solutions/
i.e. that which uses the limiting value in cell L2 (which you would set to 3 here, with a value in A1 of 20)?
Or is it just that you don't want the results in this format, i.e. with "X"s in the appropriate rows, and would rather just see a list as in the example you give?
Regards
http://excelxor.com/2015/02/10/which-numbers-add-up-to-total-2-multiple-solutions/
i.e. that which uses the limiting value in cell L2 (which you would set to 3 here, with a value in A1 of 20)?
Or is it just that you don't want the results in this format, i.e. with "X"s in the appropriate rows, and would rather just see a list as in the example you give?
Regards
Hi XOR LX ,
I prefer to see the results as a list, like the example I gave .
I have a question about a table with 1000 rows, I built a formula to neutralize the 280 lines, unwanted,
=SUM(N(MMULT(--(B11:C1010-A11:B1010=0),{1;1})=1),N(MMULT(--(A11:A1010-C11:C1010=0),1)=1))
How do I insert it into the formula,
=INDEX($A$11:$C$1010,AGGREGATE(15,6,(ROW($A$11:$C$1010)-ROW($A$11)+1)/(MMULT($A$11:$C$1010,{1;1;1})=20),ROWS($J$1:J1)),{1,2,3})
Regards
David
I prefer to see the results as a list, like the example I gave .
I have a question about a table with 1000 rows, I built a formula to neutralize the 280 lines, unwanted,
=SUM(N(MMULT(--(B11:C1010-A11:B1010=0),{1;1})=1),N(MMULT(--(A11:A1010-C11:C1010=0),1)=1))
How do I insert it into the formula,
=INDEX($A$11:$C$1010,AGGREGATE(15,6,(ROW($A$11:$C$1010)-ROW($A$11)+1)/(MMULT($A$11:$C$1010,{1;1;1})=20),ROWS($J$1:J1)),{1,2,3})
Regards
David
Last edited:
XOR LX
Active Member
Doesn't that set-up require you to have first listed all of the combinations, though? I thought you wanted a solution which generated these on its own?
And I assume that your construction is to be passed as a multi-cell array formula? Otherwise I don't see how your passing an array - {1,2,3} - as INDEX's column_num parameter will have any effect. Have you tested your formula?
I'm worried that this thread is now splitting into two separate questions. Can you re-clarify what your intentions are? Do you want to now pursue this new query from your last post? Or do you want me to present a version of my set-up at the link I posted which will generate the returns as you require?
Regards
And I assume that your construction is to be passed as a multi-cell array formula? Otherwise I don't see how your passing an array - {1,2,3} - as INDEX's column_num parameter will have any effect. Have you tested your formula?
I'm worried that this thread is now splitting into two separate questions. Can you re-clarify what your intentions are? Do you want to now pursue this new query from your last post? Or do you want me to present a version of my set-up at the link I posted which will generate the returns as you require?
Regards
