Excel help

[Mr White]

Hi,

I play minivoetbal, indoor football, indoor soccer, ... Whatever you like to call it as long as there very small goals involved and 2 teams of 5 each.
On occasion we as a team play amongs ourselves. The problem is that we don't seem to be able to form ourselves in 2 teams of equal skill. We all have our strengths, weaknesses and preferred positions without any player being quite the same type as the other.

Somebody heard about another team that used an excel-sheet to determine team compositions. Several weeks have past since and we've been unable to get a hold of this excel-file. Finaly I decided to give it a go myself and design such a file myself.
I had this idea to keep a record of all matches played amongst ourselves and give every player points according to wether they won or not AND keeping in mind what position they played.
If my team won and I played my favorite position, tnen Mr White position 1 would be given 3 points for example.
The excel-sheet would keep track of all the points accumilated by every player on every position seperatly and would then generate 2 teams of rougly equal points.
And that's where I got stuck ...

What functions could be helpful?
Where can I find good information for this?
Maybe you know of something that already exists that can serve this purpose?

[Hazkali]

Well, the brute-force approach would be to have for each player,

{table]Player Name| Position | Match 1 | Match 2

Alice | Forward | 1 | -
| Centre | - | 1
| Back | - | -
Bob | Forward | - | -
| Centre | 2 | -
| Back | - | -1
[/table]

where the number is some value corresponding to their performance in that match (obviously, expand the positions to suit your sport). Then average their score for each position in a separate table, like:


{table]Player Name| Position | Average Score

Alice | Forward | 1
| Centre | 1
| Back | 0
Bob | Forward | 0
| Centre | 2
| Back | -1
[/table]

In this example, then, Alice is equally good in centre or forward, Bob is best in centre and bad in back. This would rely on some ranking system, however (which would need to be impartial, if not objective).

If you want, you could do things like finding standard deviation and other statistical stuff on the values, to see if your teammates are improving generally and/or in a specific position, and how consistent they are. You could see what the average of different team permutations are, but I can't think offhand how you'd do that in Excel.

I would say (given my limited knowledge of all things football) that a basic system might be:


1 point for a win
0 points for a draw
-1 points for a loss
1 point for a scored goal
-1 points for no goal attempts in the match (attackers only)
1 point for a saved goal (goalies)
-1 points for any unsaved goals (goalies)
0-5 points for performance

[Manga Shoggoth]

The big problem with what you are describing is that it needs a database rather than an Excel spreadsheet. People have a tendency to try and use Excel "because they have it".

The chances are that you will have to delve a little into VBA programming to get the result you want. I can't see a simple way of doing this using the native macros.

My best guess (similar to the solution above) is:

1. Create a worksheet that lists the players - this is the index sheet.
2. Create a worksheet for each player (name the worksheet using the players name as held on the index sheet). In the worksheet, store all the positions (along the page) with the points allocated for each match played (down the page).
3. Create a worksheet to hold the final team. This can be populated via a VBA procedure that checks the individual player sheets.

If I have some time in the next couple of days I'll see what I can knock up.

[Mr White]

Well, the brute-force approach would be to have for each player,
...
where the number is some value corresponding to their performance in that match (obviously, expand the positions to suit your sport). Then average their score for each position in a separate table, like:
...
In this example, then, Alice is equally good in centre or forward, Bob is best in centre and bad in back. This would rely on some ranking system, however (which would need to be impartial, if not objective).
...

The thing is, most of us have played with each other for years. We already know what everybody's best position is. The problem is that we can rarely form 2 equally strong teams. It all depends on the form of the moment. This is meant to keep track of this form.


The big problem with what you are describing is that it needs a database rather than an Excel spreadsheet. People have a tendency to try and use Excel "because they have it".

The chances are that you will have to delve a little into VBA programming to get the result you want. I can't see a simple way of doing this using the native macros.

I thought of excel because I'm more familiar with excel than I'm with Access.
I also 'feared' that the solution involved some VBA programming. I have some limited experience with programming but nowhere near enough that I just can make something suitable just like that.


My best guess (similar to the solution above) is:

1. Create a worksheet that lists the players - this is the index sheet.
2. Create a worksheet for each player (name the worksheet using the players name as held on the index sheet). In the worksheet, store all the positions (along the page) with the points allocated for each match played (down the page).
3. Create a worksheet to hold the final team. This can be populated via a VBA procedure that checks the individual player sheets.

If I have some time in the next couple of days I'll see what I can knock up.

That's what I more or less have at the moment.

I really appreciate you putting the effort in.

[Manga Shoggoth]

The thing is, most of us have played with each other for years. We already know what everybody's best position is. The problem is that we can rarely form 2 equally strong teams. It all depends on the form of the moment. This is meant to keep track of this form.


Ah. You need to do two teams. I really need to read the specifications properly...



That's what I more or less have at the moment.


And on the journey home I thought of a better way of doing it (arranging the data by team instead of person)... Typical!



I really appreciate you putting the effort in.


No worries. Let me know your address (via MP or email - you can do either by clicking on my username on the left) so I know where to send things - no point in putting everything in the forum...

[Mr White]

You can send it to the email in my public profile.

[Manga Shoggoth]

You can send it to the email in my public profile.

I'll do that.

I coded the bulk of it last night - just got to sort out the code for selection (and reduce it from 11 positions to 5 - I really must learn to read specifications).

...And write some instructions...

Hopefully I will able to get something to you sometime tomorrow. (I have the day off, and anything is better that listening to the elections...)

[Dallas-Dakota]

Whatever you like to call it as long as there very small goals involved and 2 teams of 5 each.

You mean hockey?:smalltongue:

Though mini-voetbal, I've never heard that exact term. Zaalvoetbal, 5 vs. 5 voetbal, etc. But never mini voetball.

My guess is that you're dutch?(Or Belgian?)

You should come to the meet-up.

If we ever get around to organising it >.>

[Manga Shoggoth]

You can send it to the email in my public profile.

Actually, I can't. The Board tells me:


Sorry! That user has specified that they do not wish to receive emails. If you still wish to send an email to this user, please contact the administrator and they may be able to help.

Oh well...

The spreadsheet and instructions are ready, so if you drop me an email via the board I will send them across to you.

[Mr White]

You mean hockey?:smalltongue:

Though mini-voetbal, I've never heard that exact term. Zaalvoetbal, 5 vs. 5 voetbal, etc. But never mini voetball.

My guess is that you're dutch?(Or Belgian?)

You should come to the meet-up.

If we ever get around to organising it >.>

I'm Belgian.
the difference between zaalvoetbal en minivoetbal is de grote van de goals. minivoetbal goals roughly the size of icehockey goals. And the keeper can't use his hands.

Shoggoth, I've PMed you my mail adres.

[Manga Shoggoth]

Shoggoth, I've PMed you my mail adres.

OK. One spreadsheet on it's way. Drop me an email if there are any problems.