Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-02-04
12
Medium Priority
?
308 Views
Last Modified: 2013-02-13
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
Comment
Question by:Europa MacDonald
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
12 Comments
 

Author Comment

by:Europa MacDonald
ID: 38852272
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
 
LVL 27

Expert Comment

by:d-glitch
ID: 38852277
For N numbers:  (N!) divided by (N-3)!*3!
0
 

Author Comment

by:Europa MacDonald
ID: 38852289
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 27

Expert Comment

by:d-glitch
ID: 38852299
No easy way to do that in Excel.  You need a programming language like AWK or Perl.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 38852363
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
 

Author Comment

by:Europa MacDonald
ID: 38852382
Does Excel have a limit to the amount it can work with ?
0
 
LVL 27

Expert Comment

by:d-glitch
ID: 38852410
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 38852412
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 38852426
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
 
LVL 27

Expert Comment

by:d-glitch
ID: 38852427
>> 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
 
LVL 27

Accepted Solution

by:
d-glitch earned 2000 total points
ID: 38852433
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 38852441
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question