Random Team selection based on player cost, points

  • Hi Guys,

    New to Ozgrid and to a large degree Macros, I have been searching the site for help with selecting my team from a long list of players.

    I have a total of 453 players, the number of players each week would vary with different entries. I have a main list of players on sheet1 with cost, points won and odds. On sheet 2 I have the entry list for that week.

    I need to be able to select a total of 6 players costing no more than 15m with a range control of >15m and <13m

    Each week I will have a new sheet of entries.


  • Re: Random Team selection based on player cost, points

    try SOLVER....see the forllowing steps

    1.. Create 2 helper columns with as many rows as number of players
    2...first helper col should only contain zeros
    3...second helper col should be a product of the corresponding row of first helper col and the cost of plyers
    4...Create two sum formula one for each helper col
    5...set "Target Cell" to Value of 6 which is the sum of first helper column
    6...then set the following three constraints
    a. First helper col only to be set to Binary
    b. Sum of second helper col should be less than or equal to 15
    c. Sum of second helper col should be greater than or equal to 13

    Then you SOLVE

    I solved for the first 64 players and I got selection of player nos 2, 55, 56, 57, 59, 60 for a cost of 13

    hope that helps

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!