Solved

Determining placement of text based on a number or percentage

Posted on 2011-03-21
14
211 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:JoshFink
  • 6
  • 5
  • 3
14 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 35185345
What is the logic? How can you have 2.7 turns? Sorry if the question is ignorant, but I know more about excel than about baseball. What is the algorithm?

T
0
 

Author Comment

by:JoshFink
ID: 35185384
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
0
 
LVL 10

Expert Comment

by:answer_dude
ID: 35185574
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?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 39

Expert Comment

by:nutsch
ID: 35185613
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.
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

Open in new window

number-of-games.xls
0
 

Author Comment

by:JoshFink
ID: 35188830
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
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35191226
Why? With this reasoning, Jim shouldn't have a pick until 4, should he?
0
 

Author Comment

by:JoshFink
ID: 35191268
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
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35191589
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
0
 

Author Comment

by:JoshFink
ID: 35191593
Interesting.. Let me work on that.
0
 

Author Comment

by:JoshFink
ID: 35193426
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?
0
 
LVL 10

Accepted Solution

by:
answer_dude earned 500 total points
ID: 35194836
So, I think I figured out your algorithm.  And you're right... Mike screws it up so that it won't work as you completely expect.  The problem arises from your data... mathematically no one can be pick #2.  So, because of that Bill will actually start at Pick #7.

The algorithm for the first person (Mike) is:
    If it's the first pick, then it's their turn, and they start round 1
    otherwise, it's 1 + (round * pickevery) -- then increment their assignments and their round

The algorithm for everyone except the first person is :  
    if the Pick = Round * PickEvery, then they get a pick -- then increment their assignments and their round

So, the batting order looks like this:

Order      Name      Picks      First Pick      Picks Every
1      Mike      18      1      5
2      Josh      30      3      3
3      Jim      20      4      4
4      Bill      10      8      8
5      Frank      5      17      17

Pick order starts looking like this:
1. Mike : starts #1, his round 1 is complete, his next pick is:  1+(1*5) = 6
2. ??? (no one)
3. Josh (starts #3, his round 1 is complete, his next pick is round 2 * 3 = 6
4. Jim (starts #4, his round 1 is complete, his next pick is round 2 * 4 = 8
5. ??? (no one
6. Mike (1 + (round-1)*5 = 6), where Mike is in round 2
7. Josh (round * 3 = 6), where Josh is in round 2
8. Jim (round 2: 2*4 = 8)
9. Bill (first pick is #8)
...

and so on...

See attached:  You make changes to the Yellow area... add more rows underneath if you want -- (it's a dynamic named range there...

Run the code in Module 1... it will Put the pick order on the Sheet named "Picks"




SeasonPassPicks.xls
0
 

Author Comment

by:JoshFink
ID: 35206119
This looks good.. Thank You..

Question.. Why the double picks at the end, 78-79 & 82-83
0
 
LVL 10

Expert Comment

by:answer_dude
ID: 35207432
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...
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35207890
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
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA: Insert New column with specific format type 12 38
Automating an Excel Template using VBA and Outlook 8 24
MS Excel Formula Help 3 32
Date Formatting on Userform Print 5 26
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

809 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