USMS Virtual National Champion

Kudos to Chris S and his heroic excel efforts (must have been a ton of countif statements) - BUT - how hard would it be for the National Office and Top Ten/competition group to do this work? They have the data! And furthermore - it's clean and ready for other analyses - Pool All Stars for example - or SCY/LCM/SCM high point, etc. Writing software should not be a huge task. The national office could ask for volunteer software developers (maybe even sponsor a contest) or put it out to bid. Once the program is written, it should not be too hard to maintain (esp. with clubs and LMSCs table data apart from the TT data). It was not a huge task even for an amateur like me, it took less than 30 minutes to do even the first time when I was putzing about with it. For anyone else who wants to play with something like this, here is what I did: 1. Get the data from the USMS web site into Excel. You can download them as CSV files which Excel reads. 2. Assign points to each swim. I wrote a short function to do this, using the SELECT CASE statement. 3. Get a list of names of unique teams. I cheated here: I own the Power Utility Pack for Excel (written by guru John Walkenbach) and used one of the functions, UNIQUEITEMS. (Hey, why write from scratch what someone else has already done?) 4. Use a logical array function to add up the points for each unique team. Here is the statement: {=SUM((RC=R2C9:R4540C9)*R2C2:R4540C2)} where "RC" refers to the cell with the team name, the first range (R2C9:R4540C9) is the list of all the teams for each swim, and the second range (R2C2:R4540C2) is the point value assigned to each swim. Copy that statement down for each team and voila. Sort as needed. It can easily be done by LMSC instead of teams, of course. There may be better ways to do this, but I don't program for a living. :)

Kudos to Chris S and his heroic excel efforts (must have been a ton of countif statements) - BUT - how hard would it be for the National Office and Top Ten/competition group to do this work? They have the data! And furthermore - it's clean and ready for other analyses - Pool All Stars for example - or SCY/LCM/SCM high point, etc. Writing software should not be a huge task. The national office could ask for volunteer software developers (maybe even sponsor a contest) or put it out to bid. Once the program is written, it should not be too hard to maintain (esp. with clubs and LMSCs table data apart from the TT data). It was not a huge task even for an amateur like me, it took less than 30 minutes to do even the first time when I was putzing about with it. For anyone else who wants to play with something like this, here is what I did: 1. Get the data from the USMS web site into Excel. You can download them as CSV files which Excel reads. 2. Assign points to each swim. I wrote a short function to do this, using the SELECT CASE statement. 3. Get a list of names of unique teams. I cheated here: I own the Power Utility Pack for Excel (written by guru John Walkenbach) and used one of the functions, UNIQUEITEMS. (Hey, why write from scratch what someone else has already done?) 4. Use a logical array function to add up the points for each unique team. Here is the statement: {=SUM((RC=R2C9:R4540C9)*R2C2:R4540C2)} where "RC" refers to the cell with the team name, the first range (R2C9:R4540C9) is the list of all the teams for each swim, and the second range (R2C2:R4540C2) is the point value assigned to each swim. Copy that statement down for each team and voila. Sort as needed. It can easily be done by LMSC instead of teams, of course. There may be better ways to do this, but I don't program for a living. :)