Excel Experts?

[Sean Ashley]

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...

Sorry linear programming is a maths tool I used in my Maths degree. You wouldn't need to learn any coding just the process. Once you have done it once it becomes easier depending on the constraints .

I do have a data analysis excel book and I'm sure it is in there I may have a peek shortly.

 

[Martin Alvito]

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

That's not the way to do this. You do that when you want to optimize functions. We're working with discrete objects here.

 

[Kurt.]

Did it by hand for years, well mainly just between a small group of us. Always ended up with people complaining

Oooo errrr !!!

 

[Kurt.]

I've been trying for a while to build a random team generator for football tournaments that I do every couple months, that allows you to put in several weightings for each player and pulls out even teams

I've had so many guys in the group that are good at apps, programming, excel etc, tell me they can do it easy, but nobody seems able to. Even tried CHATGPT

Anybody here got any suggestions or possibly could do this easily?

Example:
40 players with a rating from 1 to 10 for

Attack
Defense
Stamina
Tactical
Overall

We want to be able to define how many players per team (in this example 5 per team, 8 teams) and let it pull out a balance across the teams

Have been using keamk.com which does the trick up to a point, but only allowing one basic rating from one to five

Any help would be appreciated!

Ask yourself .. “what would Everton do ?”

 

[Sean Ashley]

Something along these lines could help, only took about 20 mins to create a random team generator. Would need more complex formula's if you want unique teams.

 

[bicycleheader]

View attachment 236203
View attachment 236204
Something along these lines could help, only took about 20 mins to create a random team generator. Would need more complex formula's if you want unique teams.

Appreciate that!

Just to check this is not generating a random value for each variant? As the value for each player would be set at outset

 

[Sean Ashley]

Appreciate that!

Just to check this is not generating a random value for each variant? As the value for each player would be set at outset

You would only have to change the player stats section of the sheet where you input manually each value instead of the RANDBETWEEN function.

The right hand side will give random teams and you can keep resetting until you feel the teams are fairly balanced but random doesn't always mean fair. So I would personally not reset.

If you want players names you could add a section below with a list of players (or Data validation list if you want to be fancier) and the corresponding player number and do a VLOOKUP. If you need me to show you just ask and tomorrow I will send something

 

[bicycleheader]

You would only have to change the player stats section of the sheet where you input manually each value instead of the RANDBETWEEN function.

The right hand side will give random teams and you can keep resetting until you feel the teams are fairly balanced but random doesn't always mean fair. So I would personally not reset.

If you want players names you could add a section below with a list of players (or Data validation list if you want to be fancier) and the corresponding player number and do a VLOOKUP. If you need me to show you just ask and tomorrow I will send something

I think this gives me the first part of a possible solution. However the ideal next part would be that when it generates the team you have two controls, one which replays the randomiser because the team total value is not in the correct range OR the other which "accepts" the team and outputs it to a list and removes those players from the input data....and you go again repeating until you have 8 "fair" teams....and at that point I think a macro could be done to automate it...

 

[Kurt.]

You would only have to change the player stats section of the sheet where you input manually each value instead of the RANDBETWEEN function.

The right hand side will give random teams and you can keep resetting until you feel the teams are fairly balanced but random doesn't always mean fair. So I would personally not reset.

If you want players names you could add a section below with a list of players (or Data validation list if you want to be fancier) and the corresponding player number and do a VLOOKUP. If you need me to show you just ask and tomorrow I will send something

I was just about to say this, thanks for saving my fingers having to type.

 

[PhilM]

 

[Martin Alvito]

View attachment 236203
View attachment 236204
Something along these lines could help, only took about 20 mins to create a random team generator. Would need more complex formula's if you want unique teams.

*slow clap*

Well done, you shuffled the pack of cards. Why you would choose to do this in Excel is not clear to me.

OP's actual problem is generating outputs that satisfy a fairness condition.

 

[Sean Ashley]

*slow clap*

Well done, you shuffled the pack of cards. Why you would choose to do this in Excel is not clear to me.

OP's actual problem is generating outputs that satisfy a fairness condition.

Just being helpful and I like using Excel

 

[SerenityNigh]

*slow clap*

Well done, you shuffled the pack of cards. Why you would choose to do this in Excel is not clear to me.

OP's actual problem is generating outputs that satisfy a fairness condition.

Still doubtful excel can do this

 

[Martin Alvito]

Still doubtful excel can do this

I'm sure it can, but teaching it would be awkward. The whole point of a programming language is to make things easier than they would be in assembler language, which in turn exists because human beings rarely think in zeroes and ones.

 

[bicycleheader]

Hopefully have a solution tomorrow

Some of the comments on the thinking behind it have helped a lot

If it works and if I fully understand what it does I will advise to anybody that is interested

If it doesn't work I'll try rice and/or sawdust