Excel question

[Bob]

I'm playing underwater rugby. The next weekend my team is organizing a tournament in the national series, and I'm in charge of the technical stuff. I've gotten my hands on an excel spreadsheet to help automate the work with the game protocols etc.

Most of the stuff in there, such as generating the protocols for each match, keep a score board with an overview of the results from the different matches etc. is implemented. It is also required to make statistics after the tournament on who scored the most goals, got the most penalty time etc, but there ain't no function to do this.

An example of a protocol for one game looks like this (filled with random data for my testing only):

To the right, I've added a some cells where I tried to make some stuff for summing up how many goals were scored by the different players (the numbers filled in there now are entered manually). That way it would be much easier to go through each game at the end and sum up the total of all the matches.

The basic, is just to sum up how many 1s, how many 2s, how many 3s (being the number of the player that scored) etc in the Blue/White column, but the Goal column must also be checked, so that not for instance time penalties to a player gets counted as a goal (and vice versa). Here is where my excel skills stop, so if somebody could give me a small hint on how it could be done, I would very much appreciate it

[Anonymous]

I think you need the countif() function - I'm off to have a quick dabble in Excel and will be back shortly

[Anonymous]

Mmm, I can see where it gets tricky. Due to the need to only take those entries with an x in the Goals column the CountIf() function won't work. Bah!

I'm off to have a think, you've got me intrigued now

[Bob]

Mmm, I can see where it gets tricky. Due to the need to only take those entries with an x in the Goals column the CountIf() function won't work. Bah!

Yepp, that's exactly the same challenge I ran into and got stuck :sideways:

But thanks for thinking about it BB, much appreciated :thumbsup2:
I'll do some more thinking myself as well, and also try to talk around with some folks at the uni tomorrow to see if I can dig something up.

[Bob]

I managed to get at least some kind of solution:

I first made myself two extra columns, called 'Blue goal' and 'White goal', and to these two I copied the values from the existing 'Blue' and 'White' column, but only if the corresponding row in the 'Goal' column isn't empty. Than it was easy to use COUNTIF on these two new columns to count the numbers of goals scored by each player.

I guess I could also have done something with VB, but I've never done that too much, so for now at least this was a faster and easier solution

[spiritus]

so bob, gonna go play the 24.03? then i say GL mate :yahoo:
may the best team win

[Dingo]

If your'e playing underwater Rugby I'd call in the Dolphins to help you.....oh s**t wait, you can't, they are all being executed by the Japanese as we speak, ah well, best call in the Bulls then!!:devil: