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!