Making an Excel Scouting Database

[karomata]

Hello,

I have recently been playing with Defensive Power Rankings, attempting to find ways to greatly improve it and test it out on 2013 regional event data. I am in one of what I believe to be its most perfected stages, and I have been trying to make a calculation document, however I have run into a few problems.

To calculate these values, I need to have specific data on each match, then I average the calculated values from each match that a team competed in, to attain the final value. Other than manually tracking which matches a team played in and doing the math in calculation cells, is there a way using excel that I can have it automatically identify which matches in which cells would the team have competed in, without me having to set it to do it for that specific instance? Ex. Instead of me recording all the match data from 1511s matches into the final calculation table, the program automatically identifies which matches 1511 played in, and puts it into the table for me.

Also, instead of having to type in OPRs for each team in each match, is there a way that I can have it automatically replace the team number with the OPR, getting the data from a separate table. Ex: If I type 1511 into a cell, it automatically removes the 1511 and replaces it with the OPR from a defined table.

Once again, I am using Microsoft Excel 2013, and I understand that it is limited in its abilities. I am not an expert on Excel so before I decided that I needed to take a new approach I wanted to see if it was possible to do some of these things.

Thanks!

[Adam.garcia]

If you have all the match data in another table, you can utilize the vlookup command to pull all the raw data into a separate table, where you can do further analysis.

To get you started, here is a nice worksheet that implements the VLOOKUP feature.

http://www.chiefdelphi.com/media/papers/2815

You can find it used on the third sheet in the free speed box, where he uses a dropdown box to have users be able to select from a predefined list.

Let me know if this works, and if it does help, feel free to post on this forum for additional answers.

[Michael Hill]

Pivot tables may be a very convenient thing for you to look into. They're a bit hazy for me because I don't use them, but I know they're very useful for things like you're wanting to do.

[Basel A]

Originally Posted by karomata

Other than manually tracking which matches a team played in and doing the math in calculation cells, is there a way using excel that I can have it automatically identify which matches in which cells would the team have competed in, without me having to set it to do it for that specific instance? Ex. Instead of me recording all the match data from 1511s matches into the final calculation table, the program automatically identifies which matches 1511 played in, and puts it into the table for me.

If you'd like, I'd be happy to email/show you an example of how I did this. I've put some thought into it and haven't found a really good method in Excel, but it's not hard to make it work. Essentially just create a match index based on the schedule, then do an iterative search through the index.

[Ether]

AWK is an excellent tool for beating the raw data into submission before importing it into your spreadsheet.

Once the data is in the spreadsheet, you can use the built-in functions like vlookup and pivot tables mentioned earlier, and/or you can write macros using VBA.

Attached is a very simple stripped-down example how to use the built-in VLOOKUP function. In cell A1, enter any of the values in cells F1 through F12, and Excel will look up that value in the table and update the corresponding value in cell B1.

[Michael Hill]

Originally Posted by Ether

AWK is an excellent tool for beating the raw data into submission before importing it into your spreadsheet.

Once the data is in the spreadsheet, you can use the built-in functions like vlookup and pivot tables mentioned earlier, and/or you can write macros using VBA.

Attached is a very simple stripped-down example how to use the built-in VLOOKUP function. In cell A1, enter any of the values in cells F1 through F12, and Excel will look up that value in the table and update the corresponding value in cell B1.

lol @ beating data into submission. While I use awl on a daily basis for quick and dirty stuff, I prefer Perl as my gun in a knife fight.

[D.Allred]

Originally Posted by karomata

To calculate these values, I need to have specific data on each match, then I average the calculated values from each match that a team competed in, to attain the final value. Other than manually tracking which matches a team played in and doing the math in calculation cells, is there a way using excel that I can have it automatically identify which matches in which cells would the team have competed in, without me having to set it to do it for that specific instance? Ex. Instead of me recording all the match data from 1511s matches into the final calculation table, the program automatically identifies which matches 1511 played in, and puts it into the table for me.

Also, instead of having to type in OPRs for each team in each match, is there a way that I can have it automatically replace the team number with the OPR, getting the data from a separate table. Ex: If I type 1511 into a cell, it automatically removes the 1511 and replaces it with the OPR from a defined table.

I use similar techniques with our Excel scouting system. VLOOKUP will not add or average your raw match data. Pivot tables could do what you want, but you will need to organize your data in a table format – one row per robot per match. I use the SUMIF and COUNTIF to calculate the values I want instead of pivots, it just personal preference for the statistics I’m gathering from multiple worksheets.

Send me a PM if you want a copy of my scouting sheets.

[karomata]

Thanks for all of your help, it has helped speed along this process greatly! Hopefully in a few days I can prove the validity of these new DPR calculation methods and I'll post them here! Once again, thanks so much for all your help!