JoshFink
asked on
Determining placement of text based on a number or percentage
Ok.. The title is horrible but I couldn't figure out how to phrase what I'm looking for.
Let me explain what I'm trying to do.
I'm trying to put together a spreadsheet for a baseball season ticket draft that I have coming up. Currently we have 5 people though this could change in the draft. There are 83 games, this could fluctuate up and down also. I need a way to determine where to put people in the drat order.
Still confused? I am. Here is what I'm currently doing by hand.
83 Games
Josh - 30 games - One pick every 2.7 turns
Jim - 20 Games - 4.15
Mike - 18 Games - 4.61
Bill - 10 Games - 8.3
Frank - 5 Games - 16.6
What I would like to do is have a list of people in the league in a column and the number of games in the next.
A C
Josh 30
Jim 20
Mike 18
Bill 10
Frank 5
I would like the spreadsheet to go down and place the people where they should pick. One Caveat, Mike ALWAYS picks first.
1. Mike
2. Josh
3. Jim
etc.
Does that make sense at all? Am I trying to do too much?
Thanks
Josh
Let me explain what I'm trying to do.
I'm trying to put together a spreadsheet for a baseball season ticket draft that I have coming up. Currently we have 5 people though this could change in the draft. There are 83 games, this could fluctuate up and down also. I need a way to determine where to put people in the drat order.
Still confused? I am. Here is what I'm currently doing by hand.
83 Games
Josh - 30 games - One pick every 2.7 turns
Jim - 20 Games - 4.15
Mike - 18 Games - 4.61
Bill - 10 Games - 8.3
Frank - 5 Games - 16.6
What I would like to do is have a list of people in the league in a column and the number of games in the next.
A C
Josh 30
Jim 20
Mike 18
Bill 10
Frank 5
I would like the spreadsheet to go down and place the people where they should pick. One Caveat, Mike ALWAYS picks first.
1. Mike
2. Josh
3. Jim
etc.
Does that make sense at all? Am I trying to do too much?
Thanks
Josh
ASKER
Sorry. I should have explained better. The 2.7 was derived by taking 83 and diving the number of tickets each person gets.
You are correct, you can't have 2.7 turns. This is more of an approximation. Sometimes you would get a pick every 2 times, sometimes every 3. Just so it averages somewhat to be 83/# of tickets.
Does that make sense?
It's not really a baseball problem. This could have been anything. I really just need a way to distribute people over an 83 line limit based on how many times the number next to their name is divided by 83.
Josh
You are correct, you can't have 2.7 turns. This is more of an approximation. Sometimes you would get a pick every 2 times, sometimes every 3. Just so it averages somewhat to be 83/# of tickets.
Does that make sense?
It's not really a baseball problem. This could have been anything. I really just need a way to distribute people over an 83 line limit based on how many times the number next to their name is divided by 83.
Josh
Wouldn't you just loop through the list of people, let each one pick until they reached their limit of picks... then at that point you loop through the rest...
Pick order:
Mike, Josh, Jim, Bill, Frank.
After 5 rounds, Frank is done. Continue picking without frank. After 10 rounds, Bill is done. The remaining three continue to pick... and so on...?
Or... do you want to space it out so that Josh gets 6 picks for every 1 of Franks?
Pick order:
Mike, Josh, Jim, Bill, Frank.
After 5 rounds, Frank is done. Continue picking without frank. After 10 rounds, Bill is done. The remaining three continue to pick... and so on...?
Or... do you want to space it out so that Josh gets 6 picks for every 1 of Franks?
Hi Josh,
Here is what I got.
Create a file with a named range called Players with names in column 1 and number of games in column 2.
Run the attached code. Update the constant if your number of games changes.
Thomas
Attached file contains it.
Here is what I got.
Create a file with a named range called Players with names in column 1 and number of games in column 2.
Run the attached code. Update the constant if your number of games changes.
Thomas
Attached file contains it.
Sub GetTickets()
Dim destSheet As Worksheet, lgPlayerLoop As Long
Dim rgPlayers As Range, strPlayer As String, dblMaxUrgency As Double, dblUrgency As Double
Const numTix As Long = 83
Application.ScreenUpdating = False
Set rgPlayers = Range("Players")
Set destSheet = Sheets.Add
With destSheet
.Cells(1, 1) = "Mike"
For i = 2 To numtix
dblMaxUrgency = 0
For lgPlayerLoop = 1 To rgPlayers.Columns(1).Cells.Count
If Application.WorksheetFunction.CountIf(.Columns(1), rgPlayers.Cells(lgPlayerLoop, 1)) = 0 Then
dblUrgency = rgPlayers.Cells(lgPlayerLoop, 2) / numTix
Else
dblUrgency = rgPlayers.Cells(lgPlayerLoop, 2) / numTix _
- Application.WorksheetFunction.CountIf(.Columns(1), rgPlayers.Cells(lgPlayerLoop, 1)) _
/ Application.WorksheetFunction.CountA(.Columns(1)) _
End If
If dblMaxUrgency < dblUrgency Then
dblMaxUrgency = dblUrgency
strPlayer = rgPlayers.Cells(lgPlayerLoop, 1)
End If
Next
Cells(i, 1) = strPlayer
Debug.Print vbCrLf
Next
End With
Application.ScreenUpdating = True
End Sub
number-of-games.xls
ASKER
answer_dude : I would want it the second way. Basically, Frank doesn't get his first pick til pick 16 and Bill doesn't get his first pick til pick 8.
Thomas : This looks good. It's almost right. Here is how the picks lay out. Bill should't have a pick til 8 and Frank a pick til 16.
Mike
Josh
Jim
Bill
Josh
Frank
Jim
Josh
Mike
Josh
Jim
Mike
Bill
Josh
Jim
Josh
Thomas : This looks good. It's almost right. Here is how the picks lay out. Bill should't have a pick til 8 and Frank a pick til 16.
Mike
Josh
Jim
Bill
Josh
Frank
Jim
Josh
Mike
Josh
Jim
Mike
Bill
Josh
Jim
Josh
Why? With this reasoning, Jim shouldn't have a pick until 4, should he?
ASKER
You are correct. Jim doesn't have a pick til 4. Bill should have a pick at 8 and Frank at 16.
I think the whole Mike gets first pick thing screws things up.
What if we took mike down -1 , so he gets a pick every 4.8 times - 83/(18-1)
I think it's the whole Bill and Frank part though that needs tweaking. I can look at the code as well. I probably should to see what is going on.
Thanks again for the help
Josh
I think the whole Mike gets first pick thing screws things up.
What if we took mike down -1 , so he gets a pick every 4.8 times - 83/(18-1)
I think it's the whole Bill and Frank part though that needs tweaking. I can look at the code as well. I probably should to see what is going on.
Thanks again for the help
Josh
What you need more is to establish your decision tree. What do you consider when you make your pick order, what are all the parameters. Then we can build that decision tree in a macro.
T
T
ASKER
Interesting.. Let me work on that.
ASKER
I was thinking that this might be an easier solution like this.
Start at the bottom and work your way up. i.e.
Frank has 5 picks, so roughly one every 16-18 picks. Fill those in first. I alternated picks between 16 and 17 spaces apart.
17
32
49
65
81
Then I started with the next person Bill:
8
17
25
34
42
51
59
68
77
Then I would go about filling in the rest. If a pick happens to overlap on another pick I would move it down one as I would give priority to people with less picks.
Does that make sense?
Start at the bottom and work your way up. i.e.
Frank has 5 picks, so roughly one every 16-18 picks. Fill those in first. I alternated picks between 16 and 17 spaces apart.
17
32
49
65
81
Then I started with the next person Bill:
8
17
25
34
42
51
59
68
77
Then I would go about filling in the rest. If a pick happens to overlap on another pick I would move it down one as I would give priority to people with less picks.
Does that make sense?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This looks good.. Thank You..
Question.. Why the double picks at the end, 78-79 & 82-83
Question.. Why the double picks at the end, 78-79 & 82-83
Rounding error. some of the people have 2.7 turns but I round to 3. Eventually, people are going to need double picks in order to get all of their picks in.
Just for fun, add someone with 200 picks to the bottom of the list (only fill out column A and B) and rerun it... you'll see that they get a lot of repeat picks...
Just for fun, add someone with 200 picks to the bottom of the list (only fill out column A and B) and rerun it... you'll see that they get a lot of repeat picks...
To explain my algorithm (comment #35185613), I take into account the gap between each person's ideal distribution and the current one and allocate the ticket to the person who's furthest from his ideal balance.
Thomas
Thomas
T