[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

how many combinations of three numbers can be found in any given quantity

I have to work out and write down (on a spreadsheet):

how many groups of three numbers can be extracted (without repetition) from 80.

as an illustration:

there is only one group of three numbers in 3 (1,2,3)
there are four groups of three numbers in 4 (1,2,3  1,2,4  1,3,4  2,3,4)
there are 10 groups of three numbers in 6 (see attached worksheet)

I hope this calculation makes sense.

I need help with the formula for determining how many combinations, but in addition I also need them written out in a spreadsheet in an ordered way similar to that arranged in the attached worksheet.

I would be very grateful for help
sample-3.xls
0
Europa MacDonald
Asked:
Europa MacDonald
  • 5
  • 3
  • 3
  • +1
1 Solution
 
Europa MacDonaldChief slayer of dragonsAuthor Commented:
I have =COMBIN(80, 3) for excel which gives me 18,424 possibilities

Now, what would be great would be a way of listing them all out in order.

1 2 3  (each number to its own cell)
1 2 4
1 2 5
1 2 6.... etc
0
 
d-glitchCommented:
For N numbers:  (N!) divided by (N-3)!*3!
0
 
Europa MacDonaldChief slayer of dragonsAuthor Commented:
thankyou d-glitch, I have that already

I need some sort of code that will list them out, so that I can count the occurrence of combinations in a sample
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
d-glitchCommented:
No easy way to do that in Excel.  You need a programming language like AWK or Perl.
0
 
Arthur_WoodCommented:
since there are 164,320 sets of 3 numbers in the result set, that will be quite a challenge to list each set in Excel.

AW
0
 
Europa MacDonaldChief slayer of dragonsAuthor Commented:
Does Excel have a limit to the amount it can work with ?
0
 
d-glitchCommented:
If you have a programming language and an 80-element array,  you can do it with three nested loops:

   For i = 1 to 80
       For j = i to 80
          For k = j to 80
          Print   Array( i), Array( j), Array( k)
          next k
       next j
   next i
0
 
TommySzalapskiCommented:
Arthur, it's 3! not 3 in the denominator. So there's only 82,160 possibilities.
I don't see why Excel could not handle that. It might take a bit longer than other languages, but I can't imagine it running any longer than a minute. It could be done with either VBA (fairly simple) or formulas (a bit harder).

Does the assignment specify which you need to use?
0
 
TommySzalapskiCommented:
Or since it's not an array and it's just numbers, you could just do.

row = 1;
 For i = 1 to 78
       For j = i+1 to 79
          For k = j +1to 80
            Range("A"&row).Value = i
            Range("B"&row).Value = j
            Range("C"&row).Value = k
            row = row + 1
          next k
       next j
   next i

Open in new window

0
 
d-glitchCommented:
>> Does Excel have a limit to the amount it can work with ?

Yes.  64k rows and 256 columns.  
That is plenty of space.  The problem is the available tools.
0
 
d-glitchCommented:
Correction for the program ...

  For i = 1 to 80
       For j = i+1 to 80
          For k = j+1 to 80
          Print   Array( i), Array( j), Array( k)
          next k
       next j
   next i
0
 
TommySzalapskiCommented:
64k is Excel 2003. The later ones will handle the 82160 just fine.
And lose the semicolon in the first line of my code
Also, combin(80,3) should give 82160 which is what I calculated earlier.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now