Permutations

LeeHitchen · May 14th, 2002, 04:46 AM

Hi there.

I have 11 items and want to determine how many combinations I can have where one of the Items 9 in this case item 1 is mandatory) and the number of items within each combination totals 5. I'm aware of the PERMUT facility but unfortunately this also counts combinations with differing orders. ie Item1, Item2, Item3, Item4, Item5 is one count. Item1, Item3, Item2, Item4, Item5 is another!

All I want to determine is the total number of combinations (with mandatory unit) irrespective of the order of items.

Any help would be gratefully appreciated.

Regards

Lee

Chris Davison · May 14th, 2002, 05:22 AM

Morning Lee,

I think the =COMBIN function may do this

=PERMUT(100,3) gives 970,200

=COMBIN(100,3) gives only 161,700

_________________
Hope this helps,
Chris
(Excel '97, Windows ME)

so in your case, you can ignore the "1" as it just gets tacked on to any combination, it's any 4 from 8 :

=COMBIN(8,4) gives 70

adding "1" to each of them still gives the 70 results just with "1" in them

(of course, for variable sets, just replace the hard-coded numbers with cell references containing your numbers or COUNT formulae etc)

[ This Message was edited by: Chris Davison on 2002-05-14 04:27 ]

LeeHitchen · May 14th, 2002, 05:29 AM

Hi Chris

Thanks for the World Cup Predictions spreadsheet. Have already got 28 signed up with more eager to join!

With regards to my query I've also looked at COMBIN but it also counts like items which appear in differing orders (ie 1,2,3,4,5 is one count & 1,3,2,4,5 is another) Also it fails to take in to account that item 1 is mandatory in all combinations.

Any other ideas before I top myself?

Chris Davison · May 14th, 2002, 06:48 AM

Lee,

are you sure ?

any 2 from 5 (using my left hand here...!) ignoring which way round they are, is 10 combinations.... 20 if I put them in any order

=combin(10,2) is 10

adding a prefix (ie my right hand thumb) to all of these still gives 10

am I being thick ?!?!

edit...... yes I am.... your mandatory adds an extra perm.... sorry Lee !

[ This Message was edited by: Chris Davison on 2002-05-14 05:53 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 14th, 2002, 04:46 AM	#1
LeeHitchen New Member Join Date: Mar 2002 Location: England Posts: 26	Hi there. I have 11 items and want to determine how many combinations I can have where one of the Items 9 in this case item 1 is mandatory) and the number of items within each combination totals 5. I'm aware of the PERMUT facility but unfortunately this also counts combinations with differing orders. ie Item1, Item2, Item3, Item4, Item5 is one count. Item1, Item3, Item2, Item4, Item5 is another! All I want to determine is the total number of combinations (with mandatory unit) irrespective of the order of items. Any help would be gratefully appreciated. Regards Lee

May 14th, 2002, 05:22 AM	#2
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Morning Lee, I think the =COMBIN function may do this =PERMUT(100,3) gives 970,200 =COMBIN(100,3) gives only 161,700 _________________ Hope this helps, Chris (Excel '97, Windows ME) so in your case, you can ignore the "1" as it just gets tacked on to any combination, it's any 4 from 8 : =COMBIN(8,4) gives 70 adding "1" to each of them still gives the 70 results just with "1" in them (of course, for variable sets, just replace the hard-coded numbers with cell references containing your numbers or COUNT formulae etc) [ This Message was edited by: Chris Davison on 2002-05-14 04:27 ]

May 14th, 2002, 05:29 AM	#3
LeeHitchen New Member Join Date: Mar 2002 Location: England Posts: 26	Hi Chris Thanks for the World Cup Predictions spreadsheet. Have already got 28 signed up with more eager to join! With regards to my query I've also looked at COMBIN but it also counts like items which appear in differing orders (ie 1,2,3,4,5 is one count & 1,3,2,4,5 is another) Also it fails to take in to account that item 1 is mandatory in all combinations. Any other ideas before I top myself?

May 14th, 2002, 06:48 AM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Lee, are you sure ? any 2 from 5 (using my left hand here...!) ignoring which way round they are, is 10 combinations.... 20 if I put them in any order =combin(10,2) is 10 adding a prefix (ie my right hand thumb) to all of these still gives 10 am I being thick ?!?! edit...... yes I am.... your mandatory adds an extra perm.... sorry Lee ! [ This Message was edited by: Chris Davison on 2002-05-14 05:53 ]