Counting Combinations Question

  • Thread starter Thread starter JimS
  • Start date Start date
J

JimS

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?
 
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
 
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.
 
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
 
Jim,

You could use formulas to parse out the strings into separate cells for
processing,
Click to expand...

This sounds like a plan.
or you could use a UDF, if you are not averse to having macros
Click to expand...

I'm not averse to macros, but I would rather avoid them if I can.
in your workbooks. Also, note that your formula doesn't work for numbers
greater than 9 - you could count the commas and add one...
Click to expand...

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.
 
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.
 
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
 
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.
 
Back
Top