Combinations calculator

[mark-n-maria]

Hi i'm new to this forum and would like some help if anyone out there can.
I run a football competition amongst my friends and it seems it getting more popular, but i would like to know what is a good amount before it becomes oversaturated with duplicate entries. The question is can excel calculate the possible combinations picking any 5 teams out of 20 in the league with all the teams having a value of between 1 -12 points (some teams have the same figure according to thier chances of winning) and the total of the 5 teams must not go over say 28. (this figure and the values assigned would change each year).
Am i asking too much of excel?

any help would be appreciated.

[shg]

The number of combinations of 20 things taken 5 at a time is =COMBIN(20, 5) = 15504.

I don't understand the rest of your question.

[mark-n-maria]

The number of combinations of 20 things taken 5 at a time is =COMBIN(20, 5) = 15504.

I don't understand the rest of your question.

Thanks for your reply, but it is not that simple i'm afraid. I will try and explain further the competition as it may help, here are the selections:

Arsenal 9 points
Aston Villa 7 points
Birmingham 5 points
Blackburn 4 points
Blackpool 1 points
Bolton 3 points
Chelsea 12 points
Everton 7 points
Fulham 5 points
Liverpool 8 points
Manchester City 8 points
Manchester United 12 points
Newcastle 2 points
Stoke 4 points
Sunderland 4 points
Tottenham 8 points
West Brom 1 point
Westham 3 Points
Wigan 3 Points
Wolves 3 points

You must pick 5 selections and their total points must be 28 or under (stops you picking the 5 best teams)

Now I need to know if excel can work out the possible combinations that there are and if possible be an easy task to alter the total figure (28) or the teams value points to increase or decrease the combinations to suit the amount of entrants taking part.

Once again thanks in advance for any replies.

[foxguy]

Hi mark-n-maria;

No Excel doesn't have any formulas or functions that will do that for you directly.

Are you just asking if it's possible to 1) list every possible 5 team selection 2) add up the points for each combination 3) count the number that are under 28 points?

If that's all there is to it, Yes I could design a macro that would do it for you, and create a button that would launch it. Probably take less than 20 minutes to design it.

[martindwilson]

there you go,there you go,there you go!

[mark-n-maria]

there you go,there you go,there you go!

Omg ! thankyou MartindWilson and foxguy, that is it!, you guys are so clever!,
If i was wearing a hat i would take it off to you.

thanks very very much!

regards

Mark

[mark-n-maria]

there you go,there you go,there you go!

Thanks for your help on this but i have just noticed this is listing every possible combination in any order, so in my competition the same teams selected in a different order would be a duplicate. is this solveable?

[foxguy]

What do you want to do when there is a duplicate. If you can explain it in words, it can probably be put into a formula.

[martindwilson]

that becomes permutations then! there are 1860480 of those!
but post small example in a workbook there is probably a work around
probably to start with sort your entries alphabetically!

[mark-n-maria]

What do you want to do when there is a duplicate. If you can explain it in words, it can probably be put into a formula.

I have probably asked the wrong question I think as i have used the wording combination and i probably should be saying permutation. I just need to know the total possible variations on picking 5 teams out of 20 with their assigned value points not breaking 28 points in total.
The file i downloaded showed 15504 total combinations with their points total (of which I could count all that were 28 and under - but they showed the same teams but in a different sequence)

[martindwilson]

its combinations you want as the order is irrelevant there are no repeated permutations on that sheet eg
you wont see

Arsenal Chelsea Everton Stoke Sunderland
and somwhere else
Stoke Sunderland Arsenal Chelsea Everton

[foxguy]

I'm sorry, I cannot find any duplicates in the file. Can you point me to 2 rows that have the same teams?

[mark-n-maria]

its combinations you want as the order is irrelevant there are no repeated permutations on that sheet eg
you wont see

Arsenal Chelsea Everton Stoke Sunderland
and somwhere else
Stoke Sunderland Arsenal Chelsea Everton

with 15504 combinations! i must have been mistaken i will look at it again, thanks

[foxguy]

Here's what you probably want to do with that file.

1) Sort the data.
1 A) Select A2 - K15505.
1 B) In the menus Data->Sort->Column F->Descending

2) Count the data
2 A) In M2 : =M3+1
2 B) Copy the formula down to M15505.

3) Filter the data
3 A) Select F1 - F15505
3 B) menus Data->Autofilter

4) Select Top Value
4 A) In F1 select "Custom"
4 B) enter "less than" & "28"

The top row in column "M" will show you how many combinations there for that #.

I'd do this for you, but the web-site keeps crashing when I try to upload the file.

[mark-n-maria]

Here's what you probably want to do with that file.

1) Sort the data.
1 A) Select A2 - K15505.
1 B) In the menus Data->Sort->Column F->Descending

2) Count the data
2 A) In M2 : =M3+1
2 B) Copy the formula down to M15505.

3) Filter the data
3 A) Select F1 - F15505
3 B) menus Data->Autofilter

4) Select Top Value
4 A) In F1 select "Custom"
4 B) enter "less than" & "28"

The top row in column "M" will show you how many combinations there for that #.

I'd do this for you, but the web-site keeps crashing when I try to upload the file.

Once agains thanks for all you help i will try your suggestions to this file (if i can't do it I can still count the possibles manually as all entrants pick teams give or take 2/3 points adrift from the maximum allowed). btw how do you mark this as "solved" ? - i have found the "rate" answer part.

[foxguy]

To mark solved:
Go back to the very first message, click "Edit", then click "Go Advanced", next to the title you can change it to "Solved"

BTW there are 7734 teams with totals under 28.

[atu23]

Hey everyone,

I'm looking at making something like a combination generator when i can list a series of numbers say 1-20 and then have them broken down into groups of 3 over a series of combinations.

is this possible?

[martindwilson]

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[WELLO]

Permutations and combinations
Hello Members am new to this Forum and i signed on to the forum to help users, but i have encountered a problem myself. I have been trying to combine some numbers and their occurrences but i can't figure it out entirely, it is getting out of control and mind blogging. I need to know how to combine or perm 3 set of numbers with no one order repeating itself but are a total of 14 rows or cells .eg.


1 2 3 2 1 3 1 3 2
2 1 3 1 2 3 ...........
1 3 2 3 2 1 ...........
3 2 1 2 1 3 ...........
1 2 3 3 2 1 ...........
2 3 1 1 3 2 ...........

............ and so on for 14 rows but i need each row to be unique in set of numbers so that no one row set is repeated in any block of 3 columns on that same row. no one block of numbers (that is one set of columns and rows should be the same) should be the same. Please can some one tell me how to go about that in excel. Like the formula or any other means to achieve that.

[martindwilson]

see post #18

[jholmes45]

Hello all new to forum, i am looking for this same formula/file/macro? i have nothing more than average knowledge of excel, please excuse my word choice if not correct. I have downloaded the file above and seems to be exactly what i am looking for EXCEPT i would like to be able to change the number of teams both size of general pool (20 in file above) and teams to make combination spreadsheet (5 in file above) ) along with the team names and values. is there and easy way for me to modify the attached file above? Thanks!!!

[Pete_UK]

Can't you read Post #18?

Start a new thread of your own, with a link back to this one if it is relevant.

Pete

[jholmes45]

i did not see page 2 before post and will do so. thanks!