Page 1 of 4 123 ... LastLast
Results 1 to 15 of 57
  1. #1
    Lounger
    Join Date
    Sep 2003
    Location
    Warwick, Queensland, Australia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Allocation criteria (Excel 97)

    How can the specific riders horse be allocated to a stable or yard depending on certain criteria??

    In RiderEntry Sheet - Entry Number A1 to A400, Name (Rider) B1 to B400 First Name (Rider) C1 to C400, Horse J1 to J400, Stable N1 to N400, Yard O1 to O 400.

    Criteria
     Each rider can have one entry (Horse) through to a maximum of five (Horses) one per row.
     Horse name can not be repeated (Once only per row).
     Entry number (1 to 400)one number per row dedicated to each entry.
     Riders choose between Stable or Yard prior to entry.
     If rider selects Stable, all horses of that rider are in stables.
     If rider selects Yard, all horses of that rider are in yards.
     Some rider's request to be Stabled or Yarded beside each other due to travel reasons.

    I have tried a number of variations but can't get it together.

    Any help is greatly appreciated.
    Regards

    Harry

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Allocation criteria (Excel 97)

    Harry,
    I see nothing in the WORD document. Is there a reason it is attached?

    I don't completely understand. So let me ask some questions and make some assumptions.
    You need to Stable or Yard 400 Horses. I don't understand completely what you want here. Are you trying to figure out where to put/allocate the 400 horses?
    How many "stable" spots and how many "yard" spots?
    How are they designated?
    Could you stable all 400 if the riders asked or do some have to be yarded? Could all 400 be yarded and not stabled? Will there always be 400 or is this from the MAX number?
    How do you stable/yard someone next to another?
    What happens if Rider A wants to stabled next to Rider B, but Rider B wants to be yarded next to rider C?
    How many different riders can a single rider request to be stabled/yarded near?

    How do you want to work out any other "inconsistencies" in "nearness" or lack of stable/yard spots(if any)?

    I guess these are a start to help my undserstanding.
    Steve

  3. #3
    Lounger
    Join Date
    Sep 2003
    Location
    Warwick, Queensland, Australia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allocation criteria (Excel 97)

    Hi Steve Sorry about the Word Doc didnt mean to send it first time on for me.

    Thanks for you reply.

    1. Stables are always booked out & fully allocated before Yards then Portables ( I am not concerned about allocating Portables electronically although feel free to do so)
    2. MAXIMUM entries is 400 (Horses) - although usually 280 to 370 on a regular basis.
    3. Maximum Stables available are - 156
    4. Maximum Yards available - 158
    5. Once all Stables & Yards are booked the flow on goes into Portable Yards.
    6. Priority of Bookings Stable allocations first until fully booked then flow on into Yards then Yard bookings until fully booked then flow on into portable yards.
    7. Each Stable & Yard caters for one horse only.
    8. Allocating Stables & Yards is done manually at the moment which is very Time consuming.
    9. If rider A wants to be beside rider B & B wants to be somewhere else next to rider C - Providing they are all in Stables or all in Yards then this allocation could happen. If due to this complications in allocation occur we will make an arbitrary decision & place as many together as possible & the other riders will be placed anywhere in the system.
    " Rider allocation requests are fulfilled by us as a courtesy only & if possible. We have the final say".
    6 Regard "Inconstancies" - "Nearness - Lack of Stables/Yards - We have the final say regard the
    Outcome of these allocations which may effect the above.

    For the record I am a voluntary Event secretary for an Equestrian Club. www.wht.org.au

    Regards

    Harry

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Allocation criteria (Excel 97)

    Some further questions and some thoughts.

    Are stables numbered 1 - 156 and 1 is next to 2, next to 3, etc? So all horses for a rider should be in consecutive order? Same questions for 158 yards
    "Booking Priority" I assume is date of order?
    Who has priority between B and C: A is first and gets stables and requests to be next to B. B has NOT put in his order. There are ONLY 4 stables left and C has the order IN first for the 4, though the REQUEST to be next to B was put in earlier. In other words does A request "block out" the stables next to him in anticipation of B's request or does C's earlier submission win out?

    Are there differences within the 156 stables or within the 158 yards, that some have preferences to the actual stable or yard positions. How do you accomadate these (I would guess using the same priority?)
    If you have 2 stables left, and the next rider has 3 horses do you split them, bump someone you have already assigned, or give them to the next rider requesting ONLY 2 stables? If there are no riders requesting 2 stables afterwards, do you bump a single horse rider to fill the stables?

    In addition to booking time, wha other factors enter into "priority" (I assume most want stables, and you end up disappoointing some by not having enough and giving them only the yard?). Do you accomodate some customers or some horses more?

    What if you have so many requests for yards that stables are NOT filled: do you remove people from their request and FORCE them into the stable (I assume stables are more expensive?) even though they want the cheaper one and you CAN accomodate them in the yard and you are only moving them to fill the stables?

    What type of "logic" do you use now to fill them? I have some ideas, but I don't want to reinvent the wheel. I don't see just one solution, but many and I don't "fully understand" how to optimize or allocate.

    What are some of the thought processes you currently use. You have to really understand how to do it, to be able to tell excel how to do it.

    What would be an optimum solution (what things do you optimize?) I imagine things like:
    All rider's horses together and near to each other
    Lower the booking date the more likely to have what requested.(oprimum is ALL getting what wanted)
    Max number of Requested neighbors nearby
    What else do you use to judge that a solution is better than another? Since there is more than 1 solution, a means of finding and comparing solutions is necessary.


    In some regards, this might be better in Access. i think access can handle the data tables/input better. I don't use access so I tend to think of excel solutions.

    I imagine creating several tables of information:
    Rider table with all the rider information and give each a unique ID
    Horse table with all Horse info and give each unique ID
    A Rider/horse table consisting of rider ID and horse ID. Horse column will be have only 1 row /horse, rider column could have 1 - 5 rows (one for each horse). this actually would allow more than 5 horses to be "owned" per rider.
    Event table for information on each event (dates, etc) that the culb sponsors or that you must figure the "allocations for") Each event should have an event ID
    Entry form table: Column for entryID (unique per entry), event ID, riderID, booking date, stable/yard preference, riderID to be next to (if there can be more than 1 requested "neighbor" this gets more complicated and should be in a different table)
    entryform subtable, with entryID and horse ID (1-5 allowed)
    As you get info on new riders you enter / change info in rider table. As you get new horse info, it goes into horsetable. As horses are bought and sold yo change the rider/horse table
    as you add events you change the event table
    when you get entries to an event you add them to the entry form and subform.

    With this setup you can be linked throughout and NOT have duplicate info in any tables.
    With the info in the entry tabel/sub table, the TOUGH part comes in now how to take that info and allocate spots (which is the crux of your problem).

    Will get more into that problem as you answer more of my questions.

    Hope this is helpful to you and I am not losing you in some of the details.

    Steve

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Allocation criteria (Excel 97)

    Hi Harry,

    Here's something to get you started. You type the names of the riders in columns B & C, stabling prefs (as S, Y or P - for Stable, Yard and Portable, respectively) in column D, and their mounts' names in E:I.

    The formulae in J:L work out the accommodation arrangements, though I haven't specifically incorporated anything to group different riders together. Unless you implement a database, I think the best approach here would be to enter grouped riders together - which would be entirely workable if you can get them to submit their entries together.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    Lounger
    Join Date
    Sep 2003
    Location
    Warwick, Queensland, Australia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allocation criteria (Excel 97)

    I reply to your further thoughts: -

    1. Stables are numbered 1 through to 156 in consecutive order & Yards are numbered 157 through to 315 in consecutive order
    2. Both Stables & Yards are in blocks of approximately 42 units per block E.g. 2 rows back to back 21 long (side by side) the physical layout is not important - NUMBERING is.
    3. "Booking priority" - is based on DATE ORDER of DATA ENTRY - although can be over ridden in certain circumstances. Refer item 4)
    4. "Are there differences" - Steve this scenario happens very infrequently - My action in general terms is NO.
    This decision can be over ridden in some circumstances, such as when the rider/s in question are top grade riders & attract a larger spectator group. We initially know their requirements ahead of time from past events. A "Request" is primarily acknowledged upon receipt of the ENTRY form & assessed against background knowledge.

    I hope all of this does not sound ambiguous. What it does mean Steve is that what ever we do Excel wise it needs to have a little flexibility (that is be able to change some variables on the run.) I do this in LOOKUP regard entry fees etc now.
    5. "Stable/Yard preferences" - The odd rider (very few) ask & are catered for if possible. A small
    number approximately 7 have "their old spot" In essence the same decisions & selection applies as
    above. The majority of riders take the allocation as it comes.
    6. "Splitting or Bumping" - does occur when stables are limited, then higher grades ( Class's
    1,2,3,4) Riders could bump a lower grade (Class's 5,6,7,8,9,10,11,12).
    The lower grades are then relegated to Yards.
    7. "In addition to booking time" - answer Yes - Refer above as the reasons are the same.
    8. Stables always have a much higher demand & are ALWAYS filled first.
    9. My logic to allocate Stables & Yards manually now follows all of the above reasons (Not by the
    Heart E.g. favorites)
    10. My thought processes are refered to in items 3 through to 9. Tell me if you want to be more
    precise although I believe it should not be too RIDGED. This is the challenge, we need to give it a
    little flexibility.
    11. "Optimum Solution" - you are pretty much on the button with some slight changes.
    All horse together (same rider) or near each other (neighbor).
    "Lower booking date". - An entry closed date is always set aprox 4 weeks prior to Event Date.
    "ALL get what wanted" - Can never do it due to far too many variables that would just keep on coming up & evolving. There is always a lock/cut off point for a request - that is when it becomes too complicated & inconveniencing too many people.
    Max Numbers Request neighbors - Aprox 7 riders with max horses Aprox 21.
    "Compare Solutions" - refer all of above plus - Rider background, rider courtesy, is a good
    Ambassador to the sport, can attract others to the sport (Riders who show these points always gain preference) this can be expressed in some flexibility of data input.
    "More than one Solution"- Life is like that to Steve. I believe that given the circumstances, which in some cases are not clear cut, flexibility in data input may prevail.

    Hope I haven't dampened your thoughts too much with too many IF's or BUT'S. I figure that you know many more paths Excel could take us than what I do in fact I became very confused with how to set it up at a very early stage, hence my post. I am very grateful for all of your input. May I suggest you click on www.wht.org.au, which will give you a better understanding of the club & this challenge.
    Regards

    Harry

  7. #7
    Lounger
    Join Date
    Sep 2003
    Location
    Warwick, Queensland, Australia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allocation criteria (Excel 97)

    Thank you very much for your input. Unfortunately the overall criteria is fairly complicated.
    However any ideas that you may have are apreciated,
    Regards
    Harry

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Allocation criteria (Excel 97)

    You have added a few more factors not discussed:
    1) Rider "class" this would go in "rider Table"
    2) "Location preference" could also go in rider table or if it really is NOT "constant" to keep track of event to event, could just be an addition on the entry form.
    3) Rider "status" (for lack of a better term): " Rider background, rider courtesy, is a good Ambassador to the sport, can attract others to the sport" as you mentioned. Is there or do you want there to be a RANKING? (put in Rider table)

    Clarification?: When you mention about 7 max neighbor requests, I assume this is 7 overall, not 7 for any individual. My question was does a PARTICULAR rider ever request more than 1 neighbor. The scheme I proposed assumed that a rider would only choose 1 neighbor. DOes any rider choose more than 1 neighbor?

    Timing of "allocation". Do you allocate after all orders are in, do you allocate in blocks of orders or do you allocate as the orders arrive?

    Do you confirm a location (particular number of stable/yard), confirm just whether stable/yard/portable, or anything else BEFORE all the allocations have been finalized? In other words do you tell rider A that he has stable 15-18 before all others are allocated? If so can you Move him later if you needed to?

    I might be naive (remember I haven't actually tried to do one of these) but is the issue the LOGIC of allocating, the PROGRAMMING the logic, the spreadsheet manipulation or what?

    The logic seems "relatively" straightforward (though I might be missing something) if you do them all at once: can't you just take the orders and get the ones requesting stables, sort them by:
    1) date, 2) rider class 3) rider status

    Fill them in starting with the first stable and just going down. This should get you 60-80% done.

    Then look at "moving" people to particular locations. If the person HAS a spot that he has requested (and he is in the stables with this sorting scheme), just move them to that location and shift the rest. If he does NOT have a stable location: don't worry about accomodating him, his order was too late to get a stable.

    Now look at requested neighbors. If Rider A wants to next to rider B, and BOTH have stables, Move one and shift the others. (same people still have stables) if Rider B did NOT get a spot (due to priority) don't worry about it.

    Now take all the "stable riders" without a stable and all the other riders and sort THEM by:
    1) date, 2) rider class 3) rider status and repeat the process for the YARDS.

    Again look at "favorite location" and neighbors with the same logic.

    If you have any left after this, put them in portables.

    If you do not do it all at once, but as orders arise, just fill them in as you go. You can "block out" a spot NEXT to them for a requested neighbor and keep it open until the priority says you HAVE to fill it (you are filled before the "neighbor" requests).

    The programming for this is not "that complicated" if you have the data set up as I mentioned (though I haven't tried to program it). If you have it set up you could just do it copy/paste and not do a lot of coding (though eventually coding might be helpful, for everything, date entry, updates, etc).

    Working with excel to MANUALLY change it should not be too much of an issue. Depending on how much "control" you want, you can literally MANUALLY move and shift or you could write programming to do it.

    Steve

  9. #9
    Lounger
    Join Date
    Sep 2003
    Location
    Warwick, Queensland, Australia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allocation criteria (Excel 97)

    Reply again, Steve I have attached the Stable /Yard Plan to give yopu a better understanding.

    "Few more factors 1) Rider Class go Rider table - Correct 2) "Location Pref" best to be in rider form due to new riders asking & some riders not attending "this" event. 3)"Rider Status - Ranking YES good idea. "Clarification" - I said 7 as a figure I carried out a count from our last event as follows: - "Old Spot" 11 requests, 1 rider to 1 neighbor 16 requests, 2 rider to same (1) neighbor 7 requests, 5 riders to same (1) neighbor requests 2. Representing 36 riders & 97 horses. Work on the premise of 1 rider requesting 1 neighbor refer my survey.
    (This is where most of my juggling takes place)

    "Timing of Allocation" - My method - allocate after all entry's (orders) are in this gives me a better overview when allocating. It does not matter to the system if you allocate now or later YOUR call.
    Keep in mind that this now takes place on close of entry date (4 weeks prior to Event)
    ALL riders are advised of the allocation 1-week prior to the Event. NO RIDER is advised ahead of time. Late Entries (Not many) are placed where ever take what they can get

    I feel the issue is LOGIC & RANKING together with spreadsheet manipulation. THIS IS WHERE I HAVE TROUBLE figuring out a system.

    Just filling in is not the answer as I have found out. THIS is my method (Procedure);

    1 Allocate Stables FIRST they are the most in demand & can/will cause overflow.

    2 Begin by priority (RANK) Rider class - Rider Status - Date of receipt of entry - difficulty of request (Hard first) then location distribution.


    3 "Old Spot" is always the first to be allocated (Semi fixed position).

    4 "Requests" are allocated around "Old Spots" And can be placed at random if needed beginning with the largest group first providing that each individual GROUP is intact.
    Groups can be side by side if required providing the individual GROUP remains intact.

    5 The logic you mention is straightforward until hitting the Stable/Yard layout.
    It has taken me up to three hours to nut plan out due to variations. That

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Allocation criteria (Excel 97)

    I wondered when the layout was going to show up!

    Since you added the layout here and not at dailyexceltips, I think I will only continue the thread here. We haven't gotten any "cross-fertilization" there and wopr handles files better. If anyone asks about it at dailyexcetips I will refer them to these threads. It will also mean we don't have to keep copying and pasting keep 2 identical threads going in 2 Q/A sessions.

    So the issue of only 1 or possibly 2 neighbors is incorrect and you do want to have a possibility of 7 neighbors!

    So lets, get to the layout questions/discussion. If some of my assumptions/understanding is incorrect, please correct and clarify:

    The numbering scheme you mentioned earlier (stables: 1-156, yards: 157-315) is NOT strictly true.
    From your diagram it looks like stables are 1-120 and 239-274, and yards are 121-238 and 275-312. I don't even see a 313-315.

    1) A particular rider's horses should all be together was one of your points. I assume that together means (for the most part) consecutive numbers. 1-4, or 3-6, etc.

    Can a rider "spread blocks" eg 20-23 and go "over the lane or should this be prevented or just "avoided" (never do it or do it RARELY or doesn't matter).

    Can they use the front and back end of a block. 1,2,119,120 (again: never, rarely doesn't matter)

    The same with around the lane: 21,22,99,100 are all "close by" is that acceptable.

    How close are C and DF blocks. Could you use 60,61,239,274 for 1 rider?

    2) Neighbors. How close to be neighbors? A lot of the same questions I had for a rider above. a Rider could have 2 "nearest neighbors" (left and right, facing the same way: Rider in 8 and 9, 7 and 10 are "nearest neighbors"). Is BEHIND (facing the other way: 112, 113 to rider in 8 and 9) or diagonals facing opposite way (113 and 110 to rider in 8,9)

    Do lanes make front and back more likely to be neighbors? for example: Stable 21 with 100, 99, or 22
    Are DF block close enough to C to be considered neighbors? 60, 61, 239, 240, 241, 242.

    Since there is really no way to have 7 "true" neighbors (most I can see is 5: left forward, right forward, facing opposite, diagonal left opposite, diagonal right opposite, and I am not sure they would want to be considered "neighbors") what are judged to be close by?

    3) The same questions on the yards as for rider (I assume the answers will be the same as the answers for stables in question 1)

    4) The same questions on the yards as for neighbors (I assume the answers will be the same as the answers for stables in question 2)

    5) stable riders Neighbors with Yard riders. I assume your diagram is NOT to scale so I ask this question. Are the stable riders in eg 110,111 NEIGHBORS to the yard riders in 130-131 or is this gap way too large? There was a question earlier of some "neighbors" going into stables and others going into yards.

    6) if stables and yards could be "neighbors", would you EVER put some horses of a rider in stables and others in a YARD even close by (I think the answer would be no, but I just want the limits)

    7) When riders request a "location" are they requesting actual stable/yard NUMBERS or stable/yard BLOCKS

    8) Rank these items from most important to least important fo me:
    Rider Class
    Rider status.
    Entry receipt date (assuming BEFORE cutoff date), late entries are ranked LATER

    The above order seems to be what I see. It almost seems that the entry date would only be used as a tie breaker (if it is ON TIME) and the REAL date criteria is "on-time" vs "late"

    You mentioned "difficulty of request" could you elaborate on what makes some more difficult?

    How I see the LOGIC (again correct/clarify), How to do it comes after the LOGIC is understood!.

    I am assuming that there are NO yard/stable neighbors, since that adds even more complications.

    I also assume that requesting a "Stable" takes precedence over a requested "neighbor". (that is if rider a requests a stable in Block C and most of the others in the group have requested stables in Block A, rider A will go to Block C (his request) rather than keep the group together.


    1) Start with all the entries
    2) Separate into 3 groups: (a)stable requests (on time), ([img]/forums/images/smilies/cool.gif[/img] yard requests (on time), Šlate entries (if any)
    3) Prioritize/rank the stable requests(2a) and sort them by ranking
    4) Get a running total of "required stables" by this ranking
    5) Break group 2a into 2 groups at the total number of the stables. (a) their running total =< total ([img]/forums/images/smilies/cool.gif[/img] running total >total.
    6) we will concentrate now on group 2a-5a. Group 2a-5b will be lucky to get into a stable and it will ONLY happen if other logistics do NOT work out. We can ignore (now) any requests for neighbors that are in this group and any requests for favorite spots in this group. Some in Group 2a-5a (the unlucky) still MIGHT not get a stable.
    7)Right now each rider (in 2a-5a) has their own (variable sized) "family" of all their horses (1-5 per family) which we WILL keep together. We can also make "groups" from other riders of requested neighbors. I would tend to group them together based on the RANKING of the highest ranked rider in the group [though there could be arguments to rank them based on the lowest, or you could even do a "weighted rank" based on rankings of each rider in the group or even weighted based on ranking of rider per horse,(it doesn't really matter, though a method MUST be chosen!)] Now you sort the GROUPS by the rankings of the group. These groups can consist of 1 rider with from 1-5 horses, or a group of maybe 7 riders each with 1-5 horses, so a group might have 1 horse or a group might have even 35 (or possibly more) horses!
    8)Get a running total of required stables by this Group ranking.
    9)Theoretically it MIGHT be possible to fill them will all the constraints, but we want "relatively simple" solutions. I would estimate you should be able to first work on 60-80% Full (this range could be tightened or shifted from experience). Determine a separation of the groups within this range of the total and break into 2 "piles": 2a-5a-9a running total < ca 70% and 2a-5a-9b > ca 70%. You will NOT break the groups at this stage, the entire group is in "pile a" or the entire group is in "pile b". Groups in 2a-5a-9a SHOULD be able to be in their groups and have their desired location (that is unless some members select locations different then their groups). Groups in 2a-5a-9b might NOT.
    10) Look at the INDIVIDUAL rider rankings of those riders in group 2a-5a-9a. In ranking order, place these riders in their chosen locations (so if 2 have the same request, the higher ranking one gets the location)
    11) now in GROUP ranking order, group by group, place the rest of 2a-5a-9a to keep the groups together or as close as possible.

    At this stage, you should have about 3/4s of the stables filled and these people should be where they want to be and by whom they want to be by.

    12) Look at the INDIVIDUAL rider rankings of those riders in group 2a-5a-9b. In ranking order, place these riders in their chosen locations (If their location is taken, they are OUT of LUCK to get it and are now treated as if they have no location preference)
    13) now in GROUP ranking order, group by group, place the rest of 2a-5a-9a to try keep the groups together. Within a group individual ranking will determine who you will "break from the group".

    At this stage, some of the "later" groups might not have a location they fit into. You might have several spots for 2 or 3 horses where they have 4 or 5. The total spots might be okay, but the "sizes" are wrong. These are now the UNLUCKY ones I mentioned earlier. They do NOT get a stable spot. [The routines for "filling" the individuals and the groups should try to "minimize" these "holes" to minimize the number of "unlucky", you might want to (in your splitting of 2a-5a and 2a-5b) use 95% or even 90% of the total instead of the total to minimize the "unlucky"]
    14) From Group 2a-5b ranked in priority, find the riders who have the right number of horses to fill in any "holes" in the stables (these are the LUCKY ones). If there are still "holes": you can fill from the "late entries ("VERY LUCKY"- late and still got stable)

    The Stables are now FULL

    15)Take any "unstabled" stable requests from (2a) and all the Yard requests (2b) and combine them.
    16)Prioritize/rank these requests and sort them by ranking (at this point we do NOT have to differentiate between yard/stable requests: they are NOW all YARD requests
    17- whatever) Follow procedures above from STABLES (3-whatever) to fill the locations. You can still "group" in the yards riders who wanted to be grouped in stables.

    If there are "Holes" in the yards you could fill with LATE entries (2c) [These are "lucky" late entries"]
    All remaining riders get portables.

    I haven't thought about any real details about HOW to tell excel to do this, as I mentioned, before you can begin to program, the LOGIC of the solution must be understood: Where are the problems? What types of permutaions will there be? all sorts of variables to consider.


    Steve

  11. #11
    Lounger
    Join Date
    Sep 2003
    Location
    Warwick, Queensland, Australia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allocation criteria (Excel 97)

    Hi Steve, Yes good idea re second para, although I thought there would have been more thoughts coming in to propagate everyone's learning & idea's.

    Yes - you are seeing the picture now. I know it is very hard due to distance & not been involved in the sport etc, etc but you are hanging in just great.

    Numbering - The stables & yards are numbered as is on the stable plan. I believe this is a silly way to number them, which makes it difficult to manage. MY intention was & still is unless you have other ideas are to re number them in an orderly manner. Now is an ideal opportunity to restructure the numbering to suit your system.

    Yard number 315 does not exist they only go to 314 - I have attached an updated plan (no typo)

    Some Specs for you to give you an idea of size: -
    Stables - 156 Yards - 158
    Both Stable & Yard Dimension - 12 feet x 12 feet
    Laneway 12 feet wide Roadway 30 feet wide
    Length of Block A - 504 feet Block B - 600 feet Block C - 336 feet
    Block D - Stables - 432 feet Block F - Yards - 480 feet
    From stable # 35 B block to lane 144 feet.
    From Stable # 35 in B block around to stable # 86 B block - 312 feet

    1) Correct - does not matter where they are so long as they are

  12. #12
    Lounger
    Join Date
    Sep 2003
    Location
    Warwick, Queensland, Australia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allocation criteria (Excel 97)

    Entry Form Attached

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Allocation criteria (Excel 97)

    Few more details:

    Still not clear about Requested locations.
    1) I understand that a rider can request to be near another rider (or several other riders) which you will try to accomodate. You (kind of) "hint at" and imply that some of these (those that TRAVEL together in the same rig) are more like (what I termed) a "family" (ie rider with several horses - NEVER separated) than other groups or riders. These groups ("extended families") should almost be "linked together" much like a family and kept together, more so than the other "groups" which are only being "neighbors" due to "preference" rather than a need.

    Is this correct? should some (at least "part-groups") be connected with a tighter "bond" than other groups or even other "parts" of the same group?

    2) Also requests for LOCATIONS. Either this was not answered or I just do not get it. If rider A requests a location, is he requesting eg a particular "Block" (A, B, C, DF), or is he requesting a particular STABLE (eg stables 1-5) that they might have had the year before. Or are the only requests for "locations" request to be (or have) a particular neighbor?

    RANKINGS
    I would not say I was in a "quandry" about the rankings. I was only wondering (and speculating on the various ways it could be done) how riders are ranked and how you would rank a group.

    3) From your last note it seems that the HORSES are EACH ranked. Are the riders ranked ALSO, or is it only the Rider?

    4) if the horses are ranked: How would you RANK the Rider? Would you "average" the rank of the horses? Take the "best" rank? Take the "worst rank"?

    I would tend to rank based on the BEST, but there are arguments for all cases.

    The Priority and ranking of the individual Rider is part of the intial "who can and who won't" get a stable location so it does have some importance.

    5) Rankings of Grouped riders. The same questions arise. How to rank the group, Average of riders in group, average of horses in group, Best rider, worst rider, best horse, worst horse, etc. Not as critical for determining stable/no stable, but is a factor in if the riders will remain grouped together.

    6) In addition to ranking, "rider status" was mentioned a little. Is rider status more of "tie-breaker" with "rank" or can it affect the priority more than that? That is to say, is a rider of rank1 ALWAYS a higher priority than one of rank2? Or could a rider of Rank 2 with a "HIGH status" move in front of a rider of Rank1 with "lower status"?
    The entry date
    7) I would tend to want to use the "date of reciept" more to determine "on time/late" versus ranking. I would rather see ranking / status as more important than someone who turned it in 1 week before the deadline vs 2 weeks. I really only seeing being used as a "tie-breaker" I have one stable left and two unstabled riders with only 1 horse apiece. Both same rank, both same status, short of flipping a coin, the entry date could be used.


    Lastly,
    8) Yes, I think you are right, instead of filling the locations, by priority, in my stage 10, Looking at group size is probably more important. Since some of the groups will be single riders with 1 - 3 horses, these should be left until the end to "fill in the holes". Allocating the larger groups first while there is more "flexibility" and less constraints is a better scheme.

    Steve

  14. #14
    Lounger
    Join Date
    Sep 2003
    Location
    Warwick, Queensland, Australia
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Allocation criteria (Excel 97)

    Yes Steve your analogy is correct & the answer to your question is YES
    2) Only a small number of riders do this & it is always a specific stable/yard.
    3) NO - Horses are not ranked only riders.

    5) No need to rank horses. The CLASS that each rider enters in should be the main criteria. Something I failed to say earlier & kind of took it for granted is that some riders enter in two or three or four different classes. So Class or class average may be the control you are looking for, with other rider attributes having a lesser role to play.
    6) Rider status goes with class you will always find that riders in the higher class's (1,2,3,4 etc) carry a higher status than say riders in class's 7,8,9,etc.
    7) Yes that is the way I see it but figured this could be embraced in the over all structure. It really is not a major factor in the early placing of riders & as you say is only, really required for "Tie breakers"
    8) I have always found it easier to do this although electronically it can put a different slant on things at times.
    Place the riders who have selected stables or yards "Old spot" first then as discussed.

    Regards Harry

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Allocation criteria (Excel 97)

    I guess the more detailed my questions, the more I understand! So here is the next round:

    Part A: More hypothetical questions on filling stables.
    1) You have more stables than requests for stables (lots of cheap riders want yards) You have even used the LATE yard requests. Do you:
    Leave them empty and lose the revenue from them
    FORCE "unlucky" yard-riders to take them and pay more
    Do you OFFER yard-riders a DISCOUNT to take them (charge the intermideiate price)
    Do you OFFER "special" (ones you have done business with a lot, high status etc) yard-riders an "upgrade" at no additional cost to them

    2) You have almost filled the stables. You have 2 stables left. The remaining "on-time" stable requests are for 3 or more. You have a LATE stable request with 2 horses. Your layout would allow you to "bump" a single horse rider to the yard giving you a "3-stable spot" for one of the "3-stable" ontime requests. Or you could just put in the late request and put the remaining stable requests in the yard. What do you do? Does the "ranking" of the riders make a difference in that decision?

    3) We have discussed requests for "neighbors". Do you have requests to "be FAR AWAY" from particular riders and must that be taken into account?


    Part B: New logic variants:
    I won't rehash all the logic details from <post#=292314>post 292314</post#> but just refer you to it.

    1) Before step 3 which works on INDIVIDUAL riders, I would first make "super-riders" of what we had discussed as "extended families". A rider can have 1-5 horses, a "super-rider" is a group of riders we will treat as 1 rider for allocation so that these who travel together will stay together. It really does NOT change the logic, it just acts like a rider with from 2 -even 20 horses (if you get 4 riders traveling together). These would be ranked as the average of all the riders in the "extended-family"

    Just to emphasize these "super-riders" are not groups of "neighbors" who MAY be separated and are requesting "nearness" as a "preference" rather than the "need" of the "extended-families"/"super riders" type who travel together and share equipment.

    2) in step 5 I think I would put my "capacity buffer" here and eliminate it altogether from Step 9. In step 5 I would plan on filling to about 85% (the actual percentage will depend on how much "play" there is in the filling and should be changed with experience) HERE and NOT make a further break in Step 9 of the 60-80%. Originally I took all 100% in Step2 and then broke the groups at 60-80% in Step 9. I would now just break in Step 5 at 85% and eliminate step 9.

    3) As mentioned before, in Step 11, I would put them in by larger to smaller group. No real need to rank at this time, since the object is that all should find a location in the stable.

    Part C: Dataset
    I almost feel ready to try and tackle some allocating. I have some "logic" but not enough details to actually try to program.

    I know that If I can NOT do it manually, I can NOT do it programmatically.

    BUT,
    Just because I CAN do something manually, does NOT mean I can do it programmatically.

    I have NOT attempted (except in my head a little with no real data) to do it manually. I "understand the logic" (I think) but practice is different than theory.

    Now (I think) I need to understand the "limits/ranges" of the dataset. It would actually help if I could see some datasets. A table of (I think this is all that is needed)
    Riders (Listing them as rider1 - rider"whatever" is fine)
    Number of horses
    Ranking
    Status (if you want to consider it)
    Yard/stable preference
    Optional requests
    Others traveling with them (to be treated as "extended family" / "super-rider" will not be split
    Others to be neighbors (to be grouped, if possible, may NOT work out)
    Specific Location preference

    Also What are some of the long-term ranges in these variables. [I have added some of my "guesses" that I pulled from the same place our marketing people grab budget forecasts from. If you don't know where that is a hint: last time we had to verify them we need to hire a proctologist to find the source!]

    It might be helpful to look at different "simulated datasets" to see how I would work around the difficulties and then also see how the coding works.

    1) What is the range (min number and max number) in the number of riders? (50 - 300)
    2) What is the "distribution" in the # of horses? (for example)
    1 horse 1-10%
    2 horses 15-25%
    3 horses 40-60%
    4 horses 15-25%
    5 horses 1-10%
    3) What is the distribution in ranking?
    25% 1-4 (within each about the same)
    50% 5-8 (within each about the same)
    25% 9-12 (within each about the same)
    4) What is the distribution in Status? (we haven't discussed, so I assume a equal distribution, any value equally as likely as the next)
    5) What is the percentage of LATE entries? (0-5%)
    6) How percentage of the requests are for stables? (50 - 70%)
    7) Percentage of "super-rider groups? (0-5% of total riders)
    8) Number of riders in a super-rider group?
    2 riders (70-80%)
    3 riders (15-25%)
    4 riders (1-5%)
    5+ riders (0-2%)
    9) Percentage of Neighbor requests (15-25%)
    10) Number of riders in a Neighbor group?
    2 riders (70-80%)
    3 riders (15-25%)
    4 riders (1-5%)
    5+ riders (0-2%)
    11) Percent of ridres requesting specific stables (5-15%)


    Steve

Page 1 of 4 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •