Soccer Award allocation

[pommie]

Hi all,

My first post and im asking for help

Ive just volunteered to manage a little league soccer team.
We allocate awards and the end of each match
Player of the match, most improved, best effort in training.
These wards are randomly allocated to the kids of the season.
I wanted to create a spreadsheet that would automate this for me if i only enter the names of the players.

Unfortunately i wouldn't know where to start and would appreciate your help.

Thanks

[Russell Dawson]

Hi Pommie and welcome to the forum. Attached is something that might get you started.

This might not be the prettiest solution but it may help.

Please hit the star if it does.

Hitting F9 will refresh and give you a new result.

[Russell Dawson]

Even better - see sheet 3

[pommie]

Thanks Russell for the spreadsheet, i think i can see what its doing.
Ill punch in the names and awards and see what it looks like.
One think i forgot to mention is that each kid needs to get an award, so i didnt want John to receive a 2nd award unless all players had received their first.

Again many thanks for sending this over.

Ive modified the spreadsheet with the number of players & rounds. You can see that some of the kids get multiple awards before others recieve any.

Soccer award 0.1.xlsx

Cheers
Lee

[Russell Dawson]

Hi

Hi Lee

(a real pommie?) (in exile?)

I'm not sure whether you are asking a question as you seem to contradict yourself. I'd thought of making sure that each kid gets something before anybody else gets a second prize.

A simple solution could be to just delete the named winner from the list as a prize is awarded. It's something that can be worked on but your example seems to overcomplicate matters.

Apologies if I misunderstand, but here to help. Long lunch!

[pommie]

Hi Russell,

Yeah I'm a Brit down under these days.
Sorry for the confusion, ill try and explain a bit better.

Each Football match has 3 awards that need to be given out to 3 separate players.
There are 20 games across the season.
We need to distribute the awards fairly so that every player over the course of the season gets the same amount.
The awards are not allocated on merit and act purely as a motivator, hence i would like to create a spreadsheet that calculates who gets what at the start of the season and not have to figure it out as i go.

Make sense?

[Russell Dawson]

Hi Lee,

I understand now. Not as easy as I first thought and getting beyond my capabilities Excel wise to do a random selection for thirteen numbers over a 20 week period without (or least as little as possible) duplication.

There is a way but it involves the MRAND function which requires "Add ins" and downloading from another site and I would never tell anybody to do this although I'm quite comfortable with it. There is a demo on YouTube that you can look at and it is almost what you need. http://www.youtube.com/watch?v=rYrGF8ib7WY

However, I have been giving your problem some thought and I've come up with a matrix which you can complete each week and possibly goes a bit further by way of rewarding the kids rather than randomising the awards.

The attached allows for you to enter the players number and on the right it will keep a tally of who has had what. It enables you to make a considered choice without fear of missing anybody out.

From an Excel point of view I think it's beyond me although it surely can be done and if you feel like reposting this perhaps you could rename it something like "Randomised selection without duplication".

[pommie]

Your a legend! Thanks a lot, your suggestion is excellent and just what i could use.

[Russell Dawson]

Pleasure. Don't forget to mark thread as resolved and give me a star please if appropriate. Cheers.