Combination Formula

[triumphsm]

What formula can be created to replicate the following scenario:

1. An individual has a specific amount of cash (say $100) and wishes to purchase groceries.

2. The store has many items, all with values less than $100.
- The amount of items and their corresponding values can vary.

3. The individual is required to purchase exactly ten (10) items, tallying as close to $100 but not exceeding.

What are all the combinations?

 

[Logit]

You are seeking permutations, which in Excel Formula is =PERMUT(1 st # , 2 nd #)

=PERMUT(A2,B2)

A2 and B2 refer to the cells.

Paste the formula in C2 .... Then type 100 in A2 and 10 in B2. The answer appears in C2.

This is how it appears on the worksheet:

Col A ............Col B .............Col C

Total Amount to Purchase Amout Of Each Purchase Number of Permutations 100 10 62,815,650,955,529,500,000 <tbody> </tbody>

<tbody>
</tbody>

I suspect this is homework ....

Make Google your friend.

 

[triumphsm]

Thanks, but that would only provide me with the quantity of permutations. I am needing the grouping of all the different combinations.

This is for a coworker that would not let me know what the actual scenario is, so I had to create one based on my understanding of what he is requesting.

Using the same scenario: Let's say there are 1000 items within the store and all items have a value less than $100. Items 1-10 = $1, items 11-20 = $2, etc. What are all the different combinations of items that can be purchased without exceeding $100 and not totaling less than $95?

Hope this helps to better explain what I'm looking for.

 

[shg]

So assuming all of the items cost an integer number of dollars, your question is, what are all of the 10-element partitions of 95, 96, ..., 100?

That's not a trivial result to compute.

Also, if there are multiple items with the same price, the number of combinations explodes.

 

[triumphsm]

I've come back to this problem to see if there was another way to accomplish it with a reasonable amount of results (which there may not be).

This time I will put it in terms of a sports tryout.

Total Participants = 80 names
Team Positions Available = 10 positions

Preliminary Information:
First, participants will be graded (1-5) on four (4) different categories (max score = 20). Only the participants who end up >= the cutoff total (let's say >=10) will be selected for the combination.

For this I used a simple IF statement to only include the players who met the criteria.

Main Problem:
From the participants who met the criteria I want to find out all the combinations that could make up a 10 person team. If all 80 were available I figured the formula to be C(80,10), but that obviously results in a number far too large.

Once the number of combinations is calculated I would want to see a display of all possible team combinations with the participants corresponding scores in order to determine the best possible team(s).

Any help would be greatly appreciated. And please let me know this is the same as my previous example with the resulting value being astronomical.

Thanks!

 

[Logit]

.
This may not be exactly what you are seeking ... but ... now that you have specified teams of players .... this macro will consider the rating of each player (maximum of 200 players)
and assign them to the requested number of teams, attempting to evenly disperse the talent so all teams are relatively equal. This can be adjusted as well, so the teams are not equal, your choice.

Option Explicit


Sub MakeTeams()
Dim Players(200, 3), TeamSize(10) As Integer, TeamRating(10) As Double
Dim i As Integer, r As Integer, j As Integer, c As Integer, ctr As Integer
Dim Numplayers As Integer, NumTeams As Integer, trials As Integer
Dim t As Integer, tc As Integer, MaxRating As Double, MinRating As Double
Dim MyText As String
' Written by Eric W.  1/9/2016
Application.ScreenUpdating = False
Sheets("Sheet1").Range("I2:AK16").Value = ""
' How many teams?
    NumTeams = Range("D2").Value
    If NumTeams > 10 Or NumTeams < 2 Or Int(NumTeams) <> NumTeams Then
        MsgBox "The number of teams must be an integer from 2-10."
        Exit Sub
    End If
    
' Read all the players and ratings
    r = 2
    Erase Players, TeamSize, TeamRating
    
    While Cells(r, "A") <> ""
        If r > 201 Then
            MsgBox "The number of players must be under 200."
            Exit Sub
        End If
        Players(r - 1, 1) = Cells(r, "A")
        Players(r - 1, 2) = Cells(r, "B")
        r = r + 1
    Wend
    Numplayers = r - 2
    
' Figure out the team sizes
    For r = 1 To NumTeams
        TeamSize(r) = Int(Numplayers / NumTeams) + IIf(r <= (Numplayers Mod NumTeams), 1, 0)
    Next r
    
' Make random teams
    trials = 0
    While trials < 100
        Call Shuffle(Players, Numplayers)
        
' Figure out the team ratings
        t = 1
        tc = 1
        Erase TeamRating
        MaxRating = -1
        MinRating = 11
        For i = 1 To Numplayers
            TeamRating(t) = TeamRating(t) + Players(i, 2)
            tc = tc + 1
            If tc > TeamSize(t) Then
                TeamRating(t) = TeamRating(t) / TeamSize(t)
                If TeamRating(t) > MaxRating Then MaxRating = TeamRating(t)
                If TeamRating(t) < MinRating Then MinRating = TeamRating(t)
                t = t + 1
                tc = 1
            End If
        Next i


' Max team rating - min team rating within the limit?
        If MaxRating - MinRating <= Cells(2, "F") Then GoTo PrintTeams
        
' Nope, try again
        trials = trials + 1
    Wend
    
    MyText = "Unable to find a valid set of teams in 100 tries." & Chr(10) & Chr(10)
    MyText = MyText & "You may try again using a higher MaxRatingDiff or" & Chr(10)
    MyText = MyText & "add more players to list or decrease the NumTeams"
    MsgBox MyText
    Exit Sub
    
' Print the teams
PrintTeams:
    Range("J1:AP20").ClearContents
    ctr = 1
    For i = 1 To NumTeams
        c = i * 3 + 6
        Cells(1, c) = "Team " & Chr(64 + i)
        For j = 1 To TeamSize(i)
            Cells(j + 1, c) = Players(ctr, 1)
            Cells(j + 1, c + 1) = Players(ctr, 2)
            ctr = ctr + 1
        Next j
        Cells(TeamSize(1) + 3, c + 1) = TeamRating(i)
    Next i
Application.ScreenUpdating = True
End Sub
' This team will randomly shuffle the players
' (It's really a bad sort, but with under 100 players, it should be good enough.)
Sub Shuffle(ByRef Players, ByVal Numplayers)
Dim i As Integer
Dim j As Integer
Dim a, b, c
' Assign a random number to each player
    For i = 1 To Numplayers
        Players(i, 3) = Rnd()
    Next i
    
' Now sort by the random numbers
    For i = 1 To Numplayers
        For j = 1 To Numplayers
            If Players(i, 3) > Players(j, 3) Then
                a = Players(i, 1)
                b = Players(i, 2)
                c = Players(i, 3)
                Players(i, 1) = Players(j, 1)
                Players(i, 2) = Players(j, 2)
                Players(i, 3) = Players(j, 3)
                Players(j, 1) = a
                Players(j, 2) = b
                Players(j, 3) = c
            End If
        Next j
    Next i
    
End Sub

Download link : https://www.amazon.com/clouddrive/share/MbI3Yw7GAu8d7MsN0MlVHpuOb0PSBV0PO6FHD1dopNB

Keep in mind the existing workbook looks at the % rating. I would presume you could exchange that with an average score total and achieve the same goal.

 

[shg]

Which of these numbers do you consider a reasonable (and useful) quantity to to list?

	A​	B​	C​
1​	Qualified​	Combinations​	​
2​	80​	1,646,492,110,120​	B2: =COMBIN(A2, 10)
3​	70​	396,704,524,216​
4​	60​	75,394,027,566​
5​	50​	10,272,278,170​
6​	40​	847,660,528​
7​	30​	30,045,015​
8​	20​	184,756​

 

[triumphsm]

Logit,

Thanks! I will look into this to see if it accomplishes what I am looking for.

 

[triumphsm]

MrExcel MVP,

Those are the numbers I was coming up with. Useful results would be to only show the top 30-50 best combinations using the rating system I had mentioned, along with teams by percentage of total rating score (25%, 50%, 75%, 100%) so that more "players" would be shown.

Ideal Situation:
Once the formulas are all in place and the players have been rated, it would be ideal to have a spreadsheet where the 10 selected players can be entered with their scores resulting in an overall team % comparative to the best possible outcome.