paper: New Scouting Database from Team 2834

[Ed Law]

Originally Posted by Travis Hoffman

Thanks, Ed!

Can you please explain in more detail the calculation on the WM Results tab, column G, "Wt. Avg. CCWM", when calculated in "default" mode? How is that number obtained? How is the weighting done?

The weighted average is calculated this way. This year there are 6 weeks of competition. By default, the weighted average is projected to the week after which is 7.
The OPR from each event is scaled by multiplying the reciprocal of the number of weeks between the event and week 7. For example if the event was in week 3, then the OPR (x) will multiply by 1/4. Let's say this team also attended a Week 5 event, that OPR (y) will multiply by 1/2. The weighted average will be (x/4+y/2)/(1/4+1/2). In this case, the week 5 event will have double the weight than the week 3 event since x is divided by 4 and y is only divided by 2.
I hope this makes sense. There is nothing special about this way of assigning weight. There can be many other ways to do it. The idea is the events closer to week 7 should have a higher weight than events that are earlier. I did it this way out of simplicity and convenience. You can use a quadratic function or exponential function if you wish.

[Ed Law]

Championship version 2 is out. Good news, all 4 divisions are now linked to FIRST website. You can just refresh the pages when you are at championship and everything should work. Remember each team has to play at least 3 matches before the OPR/CCWM can be calculated. And the numbers will start to stabilize as more matches are played.

I finally figured out that in Excel 2007/2010, I can still put in the link to web pages that I know the URL but the page does not exist yet. However I still need to figure out how to automate it like in Excel 2003. Microsoft changed the way how it is done and the old macro does not work anymore. This will be one of my off-season project.

By the way, I will be in St. Louis on Friday and Saturday without my team. I am the proud father of a Dean's List finalist who will be there with me as part of the 6 finalist from Michigan. I will be updating the OPR results regularly on my computer. If anybody plans to use this spreadsheet at the Championship and would like help, PM me and I will give you my cell phone number. My version of the spreadsheet has some goodies that may help your scouting team. I really have nothing to do the whole weekend there. I would be glad to help out any team if needed.

[Ed Law]

I did not realize they added some teams. Version 2b reflects the changes.

[PSHRobotics]

Thank you so much for the work you have put in!
Although it is not that big of a deal for me at the moment, I do want to let you know for other people and for the future. When I use ctrl-shift-p to refresh, I get an error that says:

Run-time error '1004':

Application-defined or object-defined error

If you need any help debugging I'd be happy to help, although my experience with complex excel functions and visual basic is very limited.

Edit: I did change the shortcut to ctrl-u and the same problem occurred.

[Vikesrock]

Originally Posted by PSHRobotics

Thank you so much for the work you have put in!
Although it is not that big of a deal for me at the moment, I do want to let you know for other people and for the future. When I use ctrl-shift-p to refresh, I get an error that says:


If you need any help debugging I'd be happy to help, although my experience with complex excel functions and visual basic is very limited.

Edit: I did change the shortcut to ctrl-u and the same problem occurred.

The links for the division data are wrong. If Ed doesn't see this and post a fixed version, I can post one when I get to my hotel tonight, probably around 11.

[Ed Law]

I did realize the links were wrong. I will be posting the updated links in a few minutes.

[Ed Law]

Version 3 is out. It contains data through Friday matches at Championship.

I put in the wrong links because the links were put in before the URL were available. For regionals/districts, event codes were used in the URL. I forgot that for Championship, the 4 divisions use the full name in the URL instead of the abbreviation code that is used on frclinks. Sorry about that. If anybody find anything else that is wrong, please let me know.

[Vikesrock]

Originally Posted by Ed Law

Version 3 is out. It contains data through Friday matches at Championship.

Thanks Ed!

[Ed Law]

Version 4 is out. This should be the final version for this year unless somebody finds a mistake. I updated the world's ranking also. 1114 is still #1 in OPR world ranking and #2 in CCWM world ranking. 148 is #2 in OPR world ranking and #1 in CCWM world ranking.

[Ed Law]

Guess who has the highest OPR out of all 4 divisions during qualifying matches (63.4). (To see answer, highlight space to the right of Answer)
Answer: 217
Guess who has the Most Win during the whole season (71)
Answer: 217
Guess who has the Highest Win to Loss difference (55)
Answer: 217

Awesome offensive machine

[Ed Law]

Originally Posted by Travis Hoffman

Thanks, Ed. I thought it was a "what have you done for me lately" kind of deal.

Another question, one you've been asked before, likely. When I attempt to open your spreadsheet in Excel 2003 (I have the Office 2007 compatibility pack), it always seems to freeze up.

Is there a way to get your spreadsheet to work as an Excel 2003 xls, or perhaps is there a way to re-save it in another 2007 format such that it can be opened in 2003?

I finally was able to save it in 2003 format. It is version 4b. After I zipped the file, the file size is actually smaller than the zipped 2007 version. Quite surprising.

[Ed Law]

The 2012 Scouting Database spreadsheet is ready. It is at http://www.chiefdelphi.com/media/papers/2174

I created links of standing and match results for all the events already so all you have to do is refresh it at the event. I will test it to make sure they work during the Week 1 events. This is the first year we are not going to a Week 1 event so I will have more time to check the results and post them immediately afterwards.

I know there are discussions of OPR before or after penalties. I will try to address them at a later time.

[Ed Law]

Week 1 results are posted. It took a little longer to get this done because FIRST changed the format of the team standing page and I have to modify quite a bit in the macro.

I also fixed all the links for all future events. They should be working now.

[Ed Law]

Good News. I was able to get the OPR of Hybrid, Bridge, Teleop and Coopertition separately. It is much easier than I thought. It took about 20 lines of code and 20 minutes. I put the OPR results in each event. I put it all the way to the right so it does not mess up the columns of other data.

Would somebody please compare my numbers with numbers published by others to see if it is consistent?

To get the True World OPR for each individual component will take more work. I will add it in the next few weeks.

[Ed Law]

Week 2 results are out. New true world ranking is posted. The highest OPR is now 36.71 from Team 2056 folowed closely by Team 67 with OPR of 36.16

All the links of week 2 events that I put in last week worked. Just use Cntl-Shirt-P to run the macro and you will get the overall OPR as well as OPR for hybrid, bridge, teleop and coopertition during the event.