Event result sheets

[firejuju]

I am a high school science teacher trying to figure out how to automate part of a competitive spreadsheet.

Our problem: We have a tournament with nine events and 20-ish schools. We input individual student results into event specific sheets, this gives us the information for the team results. We need this information to be copied to the team result so that we can see each result. Such as (4+3+5) instead of adding them together (12).

I made a huge formula using Concatenate and nested IF statements once that sorta worked, but won't anymore due to some changes in scoring.

We use macs and I would prefer the answer be a formula rather than a macro, but if it is not really possible, a mac specific solution would be needed.

See the example sheet for our setup and more information.

Thanks for your help.

[martindwilson]

i cant quite fathom the scoring ! particularly column i/2, manually fill in the table as it is.showing where the scores came from

[protonLeah]

Having looked at your worksheet, I would suggest that each event span a number of columns equal to the maximum number of participants per team. Then you can put each member score in his own cell and use a simple Σ(range) for the total per school per event.

[firejuju]

Sorry about the confusion.

The tournament has two rounds of competition, of which there are multiple rooms competing at the same time. Each student is ranked in their room from first to sixth (1 to 6). They are also given a rating of 1-50 based on their overall capabilities. In each room there can be NO ties in Rank, but there can be ties in Rating.

To determine who is the top competitors, we add the ranks together and sort ascending. If we have several rooms of each event, there might be two students with the same rank (as in the case of the students Name6, Name7 and Name8). In this case we must separate students by their rating. We add the ratings from the two rounds together (higher is better, Name6 and Name7 are "better" than Name8).

This same scoring is used in the finals by combining the scores from the two judges together. You can see how Name1 beats Name2 even though they both have the same Rank because Name1 has a higher combined rating.

To determine team points for the preliminary rounds, however, we must use the average of the ratings (thus the Column I/2). I guess I could have written it as AVERAGE(E5,G5). Some of the rules are a bit odd, but I must deal with them as they are.

I have added a sample round to the spreadsheet.

[firejuju]

protonLeah,

I'm sorry, I don't think I can do 1 cell/contestant on the "final" results page. We have no idea how many competitors from each school will show up for each event. Realistically, it could be anything from zero to 10, but there is not a limit. Adding 10 cells to the results spreadsheet will make it unmanageable and printing it for coaches to take with them would be difficult or confusing, especially if it takes several pages.

If I did do that, I still don't know how to write the formula to make it a little more automatic than me physically typing results into the team results page. If you can show me that formula, I might be able to tweak it a bit.

Thanks for your help.

[martindwilson]

perhaps this i needed another sheet though and as i havent excel 2007 here i had to use =if(isna(vlookup1),"",vlookup1) construct to get rid of value errors you could use
=iferror(vlookup1,"") instead

[firejuju]

Thanks! This worked Great!