Another Excel Question

Discussion in 'General' started by SuddenBraking, Jan 15, 2022.

  1. SuddenBraking

    SuddenBraking The Iron Price

    My mom is in charge of determining groupings for her golf league down in Florida. There's anywhere from 18-44 people who play, and her goal is to get the groups (can be either threesomes or foursomes) as equal as possible based on handicaps (said another way, having the cumulative handicap of each group be as close as possible to the other groups).

    To do this, she assigns a random number to everyone playing and creates groups that way, and then refreshes the sheet to try and get the delta between the lowest and highest groups to be less then two (not sure why that's the arbitrary number). Anyhow, I've been trying to automate this via combining the random number generator with the solver function but having no luck. Anyone have any ideas or avenues to go down? Here's some shots to show the current logic (highlighted cell is the delta between the min and max in column I).

    At this point I'm kinda at a loss for how to handle other then writing some VBA which cycles through and refreshes the random number function until a certain value is captured in J1.

    upload_2022-1-15_13-12-2.png
    upload_2022-1-15_13-12-44.png
    upload_2022-1-15_13-12-59.png
     
  2. Yzasserina

    Yzasserina sound it out

    That's interesting. Don't know that I've ever solved a problem like that. Will think about about and revert back early next week. Is the issue that you want it automated? Because the problem seems to be solved unless I'm missing something...
     
  3. Pneumatico Delle Vittorie

    Pneumatico Delle Vittorie Retired "Tire" Guy

    Why not start/use a golf handicap system? They're based on the course played along with the tees used by each and the last ten rounds played by each person.
     
  4. SuddenBraking

    SuddenBraking The Iron Price

    Yup.

    I despise not automating things. It's a true character flaw, particularly because my mom's retired and has nothing better to do than to hit refresh for five minutes straight but that just bothers me to no end.
     
    SGVRider likes this.
  5. Yzasserina

    Yzasserina sound it out

    Lol. I'm glad you said it. :crackup:
     
    SuddenBraking likes this.
  6. SuddenBraking

    SuddenBraking The Iron Price

    The file is using the player's handicaps. The goal is trying to get each group of three or four to be as equally matched as possible across the board.
     
  7. rice r0cket

    rice r0cket Well-Known Member

    Here's what I did.

    B4: =RANDBETWEEN(5,20)
    C4: =RANK($B4,$B$4:$B$33,1)+COUNTIF($B$4:$B4,$B4)-1
    D4: =ROUNDDOWN($C4/COUNT($C$4:$C$33)*$E$1,0)
    E4: =COUNT(MATCH($D$4:$D4,$D4,0))

    You'd have to move one person from group 1 to 6, but that's a rounding issue.


    upload_2022-1-15_16-4-27.png
     
    SuddenBraking likes this.
  8. pefrey

    pefrey Well-Known Member

    Sort them from lowest to highest handicap. Put the lowest with the highest, then the two middle ones. Then the second lowest with the second highest, then the next two middles. Repeat.
     
  9. rice r0cket

    rice r0cket Well-Known Member

    Nah, don't do that. The same people will always be stuck together.
     
  10. SuddenBraking

    SuddenBraking The Iron Price

    So your method of anonymizing the pairings (so that there's variation from week to week) would be changing the order in which the names appear - that works and is clever. That being said, I'm still not seeing a method to minimize the delta between groups at the end, though - your formula is working through the data set from top to bottom, but isn't iterating through to try and minimize that delta.

    Kudos to you, though - it's a clever way to attack it and gets a large chunk of the value, but I think it suffers the same fate as using the randbetween that I set my mom up with. Said another way, I *think* the way to optimize your file would be to write some VBA which sorts column B until a certain delta is reached.

    Apologies for the delay in responding, but when I saw this last night I wasn't in any shape to try and understand what it was doing :beer:
     
  11. Pneumatico Delle Vittorie

    Pneumatico Delle Vittorie Retired "Tire" Guy

    With 18 to 44 players why would you consider sending out 3 somes? You're only taking tee times away from other players by doing so and I'm sure the club isn't real happy about that.
     
  12. SuddenBraking

    SuddenBraking The Iron Price

    There's no scarcity of tee times at the club - it has two courses, and all the women playing are part of their league (and live in/at the facility) and have a set number of tee times assigned to them, regardless of how many women show up.
     
  13. Yzasserina

    Yzasserina sound it out

    So I've been mulling this over but haven't done any work yet... is a foursome optimal and a threesome overflow?
     
  14. rice r0cket

    rice r0cket Well-Known Member

    I guess write a macro to keep shuffling them?

    Every group average is within a stroke, you can keep gnashing, but there's only so much you can do with cardinal numbers.
     
  15. Pneumatico Delle Vittorie

    Pneumatico Delle Vittorie Retired "Tire" Guy

    I know this is a math question but...

    My head hurts thinking about if the good players want to be slowed down by being paired with high handicappers, is this a competition or playing for fun, are the players playing the course or each other, can the pace of play be 4 hours or less, etc?
    :confused:

    I'm out
     
  16. SuddenBraking

    SuddenBraking The Iron Price

    I believe if the amount of women playing is divisible by four, they'll do foursomes - otherwise, threesomes.

    Yeah, I don't see a way around it other than a macro.
     

Share This Page