|  | Counting Combinations Question | | |
| | | JimS |  |
|  Posted: Thu Aug 28, 2008 8:47 am Post subject: Counting Combinations Question |  |
Hi,
Here is what I have in A1 and A2 respectively:
4,5,6,7,8,9
4,5,6
I have this formula in A3 and A4:
=LEN(SUBSTITUTE(F15,",",""))
Which gives me 6 and 3 for answers.
In A5 I have (a3*a4), which = 18, or 18 combinations.
But I don't want to count the matching numbers. In this case that
would be 4,4, 5,5 and 6,6, so the answer should be 15 combinations.
How would I construct a formula in such a way to achieve this? |
| |
| | | Bernie Deitrick | |
| | Posted: Thu Aug 28, 2008 10:55 am Post subject: Re: Counting Combinations Question | |
Jim,
Don't enter mutliple values into one cell. Select A1:A2, and use Data / Text to columns, delimited,
check 'comma' and click OK to spread your values out into individual cells.
Then in A3, enter the formula
=IF(ISERROR(MATCH(A2,1:1,FALSE)),0,1)
and copy to B3, C3, etc, for as many cells as you have in row 2.
Then use the formula
=COUNTA(1:1)*COUNTA(2:2)-SUM(3:3)
to give your combinations.
HTH,
Bernie
MS Excel MVP
"JimS" <jimx22@msn.com> wrote in message news:db0db4p82gkls8dutltqqd9uki7nihl01g@4ax.com...
| Quote: | Hi,
Here is what I have in A1 and A2 respectively:
4,5,6,7,8,9
4,5,6
I have this formula in A3 and A4:
=LEN(SUBSTITUTE(F15,",",""))
Which gives me 6 and 3 for answers.
In A5 I have (a3*a4), which = 18, or 18 combinations.
But I don't want to count the matching numbers. In this case that
would be 4,4, 5,5 and 6,6, so the answer should be 15 combinations.
How would I construct a formula in such a way to achieve this?
|
|
| |
| | | JimS | |
| | Posted: Thu Aug 28, 2008 5:55 pm Post subject: Re: Counting Combinations Question | |
| |  | |
Thanks. I purposely put multiple data into one cell. I didn't copy
this data from another source. It's much easier to do it this way,
and will be easier for the people using the spread sheet. Considering
that, is there still a way to do it?
I have this data in h15 and i15:
2,3,4
3,4,5
I have the following formula in J15, which counts the combinations but
includes the matches. I just need to exclude the matches and I'm set.
=IF(I15="","",(LEN(SUBSTITUTE(H15,",",""))*LEN(SUBSTITUTE(I15,",",""))))
With this formula I get 9. I need to fix it so I get 7.
Thanks for your formula. I can use that elsewhere.
On Thu, 28 Aug 2008 08:55:00 -0400, "Bernie Deitrick" <deitbe @
consumer dot org> wrote:
| Quote: | Jim,
Don't enter mutliple values into one cell. Select A1:A2, and use Data / Text to columns, delimited,
check 'comma' and click OK to spread your values out into individual cells.
Then in A3, enter the formula
=IF(ISERROR(MATCH(A2,1:1,FALSE)),0,1)
and copy to B3, C3, etc, for as many cells as you have in row 2.
Then use the formula
=COUNTA(1:1)*COUNTA(2:2)-SUM(3:3)
to give your combinations.
HTH,
Bernie
MS Excel MVP
"JimS" <jimx22@msn.com> wrote in message news:db0db4p82gkls8dutltqqd9uki7nihl01g@4ax.com...
Hi,
Here is what I have in A1 and A2 respectively:
4,5,6,7,8,9
4,5,6
I have this formula in A3 and A4:
=LEN(SUBSTITUTE(F15,",",""))
Which gives me 6 and 3 for answers.
In A5 I have (a3*a4), which = 18, or 18 combinations.
But I don't want to count the matching numbers. In this case that
would be 4,4, 5,5 and 6,6, so the answer should be 15 combinations.
How would I construct a formula in such a way to achieve this?
|
|
| |
| | | Bernie Deitrick | |
| | Posted: Thu Aug 28, 2008 6:41 pm Post subject: Re: Counting Combinations Question | |
| | | |
Jim,
You could use formulas to parse out the strings into separate cells for
processing, or you could use a UDF, if you are not averse to having macros
in your workbooks. Also, note that your formula doesn't work for numbers
greater than 9 - you could count the commas and add one...
HTH,
Bernie
MS Excel MVP
"JimS" <jimx22@msn.com> wrote in message
news:ob0eb4d464cdkpriinnu406fh47mgr9q5j@4ax.com...
| Quote: | Thanks. I purposely put multiple data into one cell. I didn't copy
this data from another source. It's much easier to do it this way,
and will be easier for the people using the spread sheet. Considering
that, is there still a way to do it?
I have this data in h15 and i15:
2,3,4
3,4,5
I have the following formula in J15, which counts the combinations but
includes the matches. I just need to exclude the matches and I'm set.
=IF(I15="","",(LEN(SUBSTITUTE(H15,",",""))*LEN(SUBSTITUTE(I15,",",""))))
With this formula I get 9. I need to fix it so I get 7.
Thanks for your formula. I can use that elsewhere.
On Thu, 28 Aug 2008 08:55:00 -0400, "Bernie Deitrick" <deitbe @
consumer dot org> wrote:
Jim,
Don't enter mutliple values into one cell. Select A1:A2, and use Data /
Text to columns, delimited,
check 'comma' and click OK to spread your values out into individual
cells.
Then in A3, enter the formula
=IF(ISERROR(MATCH(A2,1:1,FALSE)),0,1)
and copy to B3, C3, etc, for as many cells as you have in row 2.
Then use the formula
=COUNTA(1:1)*COUNTA(2:2)-SUM(3:3)
to give your combinations.
HTH,
Bernie
MS Excel MVP
"JimS" <jimx22@msn.com> wrote in message
news:db0db4p82gkls8dutltqqd9uki7nihl01g@4ax.com...
Hi,
Here is what I have in A1 and A2 respectively:
4,5,6,7,8,9
4,5,6
I have this formula in A3 and A4:
=LEN(SUBSTITUTE(F15,",",""))
Which gives me 6 and 3 for answers.
In A5 I have (a3*a4), which = 18, or 18 combinations.
But I don't want to count the matching numbers. In this case that
would be 4,4, 5,5 and 6,6, so the answer should be 15 combinations.
How would I construct a formula in such a way to achieve this?
|
|
| |
| | | JimS | |
| | Posted: Fri Aug 29, 2008 6:30 am Post subject: Re: Counting Combinations Question | |
| | | |
On Thu, 28 Aug 2008 16:41:55 -0400, "Bernie Deitrick" <deitbe @
consumer dot org> wrote:
| Quote: | Jim,
You could use formulas to parse out the strings into separate cells for
processing,
|
This sounds like a plan.
| Quote: | or you could use a UDF, if you are not averse to having macros
|
I'm not averse to macros, but I would rather avoid them if I can.
| Quote: | in your workbooks. Also, note that your formula doesn't work for numbers
greater than 9 - you could count the commas and add one...
|
Since I will rarely use a number larger than twelve, I can use 0 for
ten, x for 11 and y for 12. Or something similar.
Now to figure out those formulas.
| Quote: |
HTH,
Bernie
MS Excel MVP
"JimS" <jimx22@msn.com> wrote in message
news:ob0eb4d464cdkpriinnu406fh47mgr9q5j@4ax.com...
Thanks. I purposely put multiple data into one cell. I didn't copy
this data from another source. It's much easier to do it this way,
and will be easier for the people using the spread sheet. Considering
that, is there still a way to do it?
I have this data in h15 and i15:
2,3,4
3,4,5
I have the following formula in J15, which counts the combinations but
includes the matches. I just need to exclude the matches and I'm set.
=IF(I15="","",(LEN(SUBSTITUTE(H15,",",""))*LEN(SUBSTITUTE(I15,",",""))))
With this formula I get 9. I need to fix it so I get 7.
Thanks for your formula. I can use that elsewhere.
On Thu, 28 Aug 2008 08:55:00 -0400, "Bernie Deitrick" <deitbe @
consumer dot org> wrote:
Jim,
Don't enter mutliple values into one cell. Select A1:A2, and use Data /
Text to columns, delimited,
check 'comma' and click OK to spread your values out into individual
cells.
Then in A3, enter the formula
=IF(ISERROR(MATCH(A2,1:1,FALSE)),0,1)
and copy to B3, C3, etc, for as many cells as you have in row 2.
Then use the formula
=COUNTA(1:1)*COUNTA(2:2)-SUM(3:3)
to give your combinations.
HTH,
Bernie
MS Excel MVP
"JimS" <jimx22@msn.com> wrote in message
news:db0db4p82gkls8dutltqqd9uki7nihl01g@4ax.com...
Hi,
Here is what I have in A1 and A2 respectively:
4,5,6,7,8,9
4,5,6
I have this formula in A3 and A4:
=LEN(SUBSTITUTE(F15,",",""))
Which gives me 6 and 3 for answers.
In A5 I have (a3*a4), which = 18, or 18 combinations.
But I don't want to count the matching numbers. In this case that
would be 4,4, 5,5 and 6,6, so the answer should be 15 combinations.
How would I construct a formula in such a way to achieve this?
|
|
| |
| | | JimS | |
| | Posted: Fri Aug 29, 2008 8:07 am Post subject: Re: Counting Combinations Question | |
| | | |
Let me ask you this. Is there an easy way to count the number of
matches in the following two cells?
127
12457
The answer would be 3.
If I can do that I've got a solution to my problem.
On Thu, 28 Aug 2008 16:41:55 -0400, "Bernie Deitrick" <deitbe @
consumer dot org> wrote:
| Quote: | Jim,
You could use formulas to parse out the strings into separate cells for
processing, or you could use a UDF, if you are not averse to having macros
in your workbooks. Also, note that your formula doesn't work for numbers
greater than 9 - you could count the commas and add one...
HTH,
Bernie
MS Excel MVP
"JimS" <jimx22@msn.com> wrote in message
news:ob0eb4d464cdkpriinnu406fh47mgr9q5j@4ax.com...
Thanks. I purposely put multiple data into one cell. I didn't copy
this data from another source. It's much easier to do it this way,
and will be easier for the people using the spread sheet. Considering
that, is there still a way to do it?
I have this data in h15 and i15:
2,3,4
3,4,5
I have the following formula in J15, which counts the combinations but
includes the matches. I just need to exclude the matches and I'm set.
=IF(I15="","",(LEN(SUBSTITUTE(H15,",",""))*LEN(SUBSTITUTE(I15,",",""))))
With this formula I get 9. I need to fix it so I get 7.
Thanks for your formula. I can use that elsewhere.
On Thu, 28 Aug 2008 08:55:00 -0400, "Bernie Deitrick" <deitbe @
consumer dot org> wrote:
Jim,
Don't enter mutliple values into one cell. Select A1:A2, and use Data /
Text to columns, delimited,
check 'comma' and click OK to spread your values out into individual
cells.
Then in A3, enter the formula
=IF(ISERROR(MATCH(A2,1:1,FALSE)),0,1)
and copy to B3, C3, etc, for as many cells as you have in row 2.
Then use the formula
=COUNTA(1:1)*COUNTA(2:2)-SUM(3:3)
to give your combinations.
HTH,
Bernie
MS Excel MVP
"JimS" <jimx22@msn.com> wrote in message
news:db0db4p82gkls8dutltqqd9uki7nihl01g@4ax.com...
Hi,
Here is what I have in A1 and A2 respectively:
4,5,6,7,8,9
4,5,6
I have this formula in A3 and A4:
=LEN(SUBSTITUTE(F15,",",""))
Which gives me 6 and 3 for answers.
In A5 I have (a3*a4), which = 18, or 18 combinations.
But I don't want to count the matching numbers. In this case that
would be 4,4, 5,5 and 6,6, so the answer should be 15 combinations.
How would I construct a formula in such a way to achieve this?
|
|
| |
| | | Pete_UK | |
| | Posted: Fri Aug 29, 2008 11:53 am Post subject: Re: Counting Combinations Question | |
Hello Jim,
with your numbers in A1 and A2, you can use this:
=SUMPRODUCT(--(ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("x1:x"&LEN(A1))),
1),A2))))
It will give you the number of matches, digit by digit.
Hope this helps.
Pete
On Aug 29, 11:07 am, JimS <jim...@msn.com> wrote:
| Quote: | Let me ask you this. Is there an easy way to count the number of
matches in the following two cells?
127
12457
The answer would be 3.
If I can do that I've got a solution to my problem.
|
|
| |
| | | JimS | |
| | Posted: Fri Aug 29, 2008 3:31 pm Post subject: Re: Counting Combinations Question | |
This works perfectly. Even if I undersood how to use all of the
separate functions in this formula...isnumber, search, mid, row,
indirect and len. I don't think I could have properly constructed a
similar formula if I had the rest of my lifetime.
I am just amazed how you guys know all this.
Thanks again, very much appreciated.
On Fri, 29 Aug 2008 04:53:31 -0700 (PDT), Pete_UK
<pashurst@auditel.net> wrote:
| Quote: | Hello Jim,
with your numbers in A1 and A2, you can use this:
=SUMPRODUCT(--(ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("x1:x"&LEN(A1))),
1),A2))))
It will give you the number of matches, digit by digit.
Hope this helps.
Pete
On Aug 29, 11:07 am, JimS <jim...@msn.com> wrote:
Let me ask you this. Is there an easy way to count the number of
matches in the following two cells?
127
12457
The answer would be 3.
If I can do that I've got a solution to my problem.
|
|
| |
| | | Pete_UK | |
| | Posted: Fri Aug 29, 2008 10:03 pm Post subject: Re: Counting Combinations Question | |
| | | |
You're welcome, Jim - thanks for feeding back.
Pete
On Aug 29, 6:31 pm, JimS <jim...@msn.com> wrote:
| Quote: | This works perfectly. Even if I undersood how to use all of the
separate functions in this formula...isnumber, search, mid, row,
indirect and len. I don't think I could have properly constructed a
similar formula if I had the rest of my lifetime.
I am just amazed how you guys know all this.
Thanks again, very much appreciated.
On Fri, 29 Aug 2008 04:53:31 -0700 (PDT), Pete_UK
pashu...@auditel.net> wrote:
Hello Jim,
with your numbers in A1 and A2, you can use this:
=SUMPRODUCT(--(ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("x1:x"&LEN(A1))),
1),A2))))
It will give you the number of matches, digit by digit.
Hope this helps.
Pete
On Aug 29, 11:07 am, JimS <jim...@msn.com> wrote:
Let me ask you this. Is there an easy way to count the number of
matches in the following two cells?
127
12457
The answer would be 3.
If I can do that I've got a solution to my problem.- Hide quoted text -
- Show quoted text - |
|
| |
|
|
