Excel Experts?

[SerenityNigh]

Thank you.!

So the ratings will be set values picked by a group of us, no randomising needed there

If we forgot about the 4 individual sets of values for now and just gave each an overall rating 1-10 the question is how to create 8 groups of 5 names without repeating a name, and as @verreauxi said making each team total rating be in the mid range…

Although I was thinking mid range may not actually be between a team of 5 players ranked as 1 and 5 ranked as 10, but perhaps needs to be specific to the total actual ratings of the top 5 and Bottom 5 players. As such the best possible team from my ratings would be 42 and worst would be 15….so midrange 28.5…..so all teams to have a total rating of between 26-31

The problem here is that you aren't randomly assigning players if you want teams to be more or less evenly distributed. I don't know what Excel can do (on the upper end, except that it has a lot of financial tools) and know nothing about VB, but maybe that's your solution. I think if you want to learn the programming you can probably figure this out with some help and generate "random assignment" based on your parameters. Otherwise I'd stick to the pencil and paper method you're using. I am doubtful excel can do what you want easily, but I would like to see it if you build it.

Seems like you want a table of player values from which you can calculate an average player value. From there you can determine the target team value (avg value x number of players). Begin by randomly assigning players to teams, but as you remove players from the pool you need to update both the remaining average player value and the min/max player value and you'll need some program/layer/application that can sort through and limit a distribution that moves teams too far from the target value (or maybe a target range). Or maybe ask a programmer to help and discard everything I wrote.

 

[bicycleheader]

The problem here is that you aren't randomly assigning players if you want teams to be more or less evenly distributed. I don't know what Excel can do (on the upper end, except that it has a lot of financial tools) and know nothing about VB, but maybe that's your solution. I think if you want to learn the programming you can probably figure this out with some help and generate "random assignment" based on your parameters. Otherwise I'd stick to the pencil and paper method you're using. I am doubtful excel can do what you want easily, but I would like to see it if you build it.

Seems like you want a table of player values from which you can calculate an average player value. From there you can determine the target team value (avg value x number of players). Begin by randomly assigning players to teams, but as you remove players from the pool you need to update both the remaining average player value and the min/max player value and you'll need some program/layer/application that can sort through and limit a distribution that moves teams too far from the target value (or maybe a target range). Or maybe ask a programmer to help and discard everything I wrote.

Yea given how many excel Experts and programmers have told me they could do this and none of have been able I was starting to think it's not as easy as I had thought

But as you are making me think now with your last paragraph I think it needs working out mathematically first as to what I need an excel formula or programmer to do. That I guess is a different skillset.

Thank you

Edit: also made it clear I might not be asking the right question!

 

[canuckblue]

Check out RAWK. Oh, sorry, those are incel experts.

 

[SerenityNigh]

Yea given how many excel Experts and programmers have told me they could do this and none of have been able I was starting to think it's not as easy as I had thought

But as you are making me think now with your last paragraph I think it needs working out mathematically first as to what I need an excel formula or programmer to do. That I guess is a different skillset.

Thank you

I think that's it, if you can draw or diagram it out logically, then you can write it in excel or program it. Most of us can conceive of the raw inputs and the desired output but the process is foggy. We're all basically underpants gnomes.

 

[Bedfordblue]

Another way is to list out the teams in column A, then place you laptop into a largish bowl of dry rice, it should have sorted itself out by the next morning.
You’re welcome.

 

[Rita_Poon]

I think that's it, if you can draw or diagram it out logically, then you can write it in excel or program it. Most of us can conceive of the raw inputs and the desired output but the process is foggy. We're all basically underpants gnomes.

niche

 

[GETEM]

I would divide the players into groups based on the overall ratings.

e.g. five players overall rated between 40-45, five players overall rated between 46-51 etc.

Then you can randomly select a player from each group to make up a team which will be reasonably fairly

 

[bicycleheader]

I would divide the players into groups based on the overall ratings.

e.g. five players overall rated between 40-45, five players overall rated between 46-51 etc.

Then you can randomly select a player from each group to make up a team which will be reasonably fairly

Thanks for reply

I think we tried something like that but a lot of the players fall into a similar bracket. So its not 5 per range unfortunately

 

[dublinbluenose]

Play around with RANDARRAY function and that will probably give you what you are looking for.

 

[Martin Alvito]

The problem here is that you aren't randomly assigning players if you want teams to be more or less evenly distributed. I don't know what Excel can do (on the upper end, except that it has a lot of financial tools) and know nothing about VB, but maybe that's your solution. I think if you want to learn the programming you can probably figure this out with some help and generate "random assignment" based on your parameters. Otherwise I'd stick to the pencil and paper method you're using. I am doubtful excel can do what you want easily, but I would like to see it if you build it.

Seems like you want a table of player values from which you can calculate an average player value. From there you can determine the target team value (avg value x number of players). Begin by randomly assigning players to teams, but as you remove players from the pool you need to update both the remaining average player value and the min/max player value and you'll need some program/layer/application that can sort through and limit a distribution that moves teams too far from the target value (or maybe a target range). Or maybe ask a programmer to help and discard everything I wrote.

The basic problem doesn't sound all that different from shuffling a pack of cards. Even in something awkward for the purpose like C or Pascal, all that is necessary is some looping to move 52 objects from one set in order to another in a random order. The only additional step this requires is figuring out how to put a thumb on the scale based on the fairness algorithm and the current state of the draft.

To get lazy about the programming that generates OP's desired result, do 1000 (or more) completely random drafts, then at random select something from the set of outputs evaluated as most 'fair' by whatever OP settles on as the fairness algorithm. It's not like memory or processing power are constraints these days. If you're concerned that could become a problem, settle on a fairness algorithm with a scalar output and keep the first five/ten/however many outputs. Boot the lowest scoring one every time an output is more 'fair' than previous drafts in the 'fair' set, and add the new observation. Discard any results that don't make the cut. That will save on memory.

OP is correct that it is vital to settle on what is 'fair' mathematically before doing any work. That's the hard part. I wouldn't do this in Excel, if I were you. I would just input the number of the player observation and the associated player value(s) into whatever programming language you want to use, tell the program how to evaluate a draft, and let it do the rest. There are ways to have your program pull from whatever spreadsheet you want to update every couple of months for the next draft.

 

[Brownie]

Just figure it out pal.

 

[Sean Ashley]

I haven't given this much thought, but if I understand you correctly you want to have randomly picked teams that are roughly fair in terms of player ratings. In thinking about this, the poorest team would consist of 5 players of rating 1, so their total rating sums to 5. The best team would have 5 players of rating 10, so their total rating sums to 50. Maybe you could have a simple rule that picks 5 random players under the constraint that the total players ratings (across all five randomly picked players) sums to >25 and <30 or something. Sorry if this doesn't make sense.

That does make sense! Now I have to think about how to do this in excel, but that at least would sort a way to do this with an overall rating per player of 10. Which would be enough of a start for this weekends tournament.

Including more variations is more a nice to have

Thanks!

Like that idea, have you heard about linear programming? That allows to build models based on constraints.

 

[bicycleheader]

Like that idea, have you heard about linear programming? That allows to build models based on constraints.

I probably should be clear in that I personally although decent at math, logic and processes I am at best average at excel and know almost nothing about programming...

 

[Huyton Layne]

Reckon writing a little program would take 2005 me no time at all. Sadly 2023 me has kids and a brain of mush.

 

[bicycleheader]

Reckon writing a little program would take 2005 me no time at all. Sadly 2023 me has kids and a brain of mush.

Ive heard a few variations on this story in the footy group unfortunately!