Quote:
Originally Posted by Joe Ross
Manual. At this point, I'm not sure that it would be much faster to put in lookup tables, as the slow part would be getting the data from the different sources and putting it in the format the lookup tables expect. I'm not sure what FiM does, it's either manual, or it's done in another spreadsheet and then only the values are posted.
|
I do the Results and Rankings for FiM so I can tell you how it is done. MAR did their own last year but I also did theirs at their request because they want to make sure we have the same numbers. This year MAR has a new person doing the Results and Rankings and I have been in touch with him to make sure we do things the same way.
I have another spreadsheet that I use to get all the data prepared and then copied to a separate file for publication. Nothing is done manually because it is so easy to introduce errors. But it is not just pressig one button either.
Award points are calculated automatically by getting data from FIRST website and through a converter (Excel lookup table) it will put the team number into the correct awards. However some events award no judges awards and some award 2 judges awards so I still have to double check. I also use a pivot table to take care of teams winning more than one award so there is no manual adding of award points.
1st and 2nd round picks info are not available through FIRST website even though we tried to ask for it for many years. The FTAs for each district put the Alliance Selection report in a Google doc folder that is shared with me so I can retrieve them when I run the report. Again lookup tables are used so there is no manual typing in team numbers and selection order.
I have macros and Excel formulas to detect and take care of substitutions automatically so it is not done manually but I have to feed that information in.
Tiebreak is the hardest part. It requires many sheets and formulas and is complicated. But it is fully automated.
All other information I already have in the Team2834 Scouting database. I have a macro to output the three highest match scores from each event and then use that in tiebreak calculations.
I don't mind sharing the spreadsheet with other regions with the person who has to do this job but it is not very easy to understand. You have to be very knowledgeable in Excel to figure out what I was trying to do. I wrote it for my own use and wasn't planned to be user friendly. Also if there are different number of events, different number of teams extra would require extensive reprogramming of the formulas which is very time consuming to setup although it is a one time thing each year. It would be easier if someone knowledgeable enough in Excel to develop their own spreadsheet rather than modifying mine. I would certainly be willing to help.