On Fri, 6 Apr 2012 07:03:22 +0000, Agrajag
wrote:
I'm in a fantasy football league I'm in that's extremely numbers-based.
Lots of data analysis to do (fictional players so it's year round with
several seasons each year).
The league provides a massive CSV file that contains all data for all
players in the entire league. This is a large-ish file with 2700 players
and all their attribute data (name, position, height, weight, strength,
intelligence, speed, etc.) There are literally dozens of attributes and
all of them appear in the master CSV even though each position only uses
a subset of them (a Center has a "Snapping" attribute (his ability to
snap the ball to the quarterback) while a Running Back would have no
such value there as he never does that.)
The main issue is to then have sheets for each position to make viewing
and analysis simpler. Plus, this data changes often so the goal is to
just be able to re-populate the master sheet as needed and have
everything else auto-populate. Nothing will happen on the master sheet.
It's just a placeholder of all the data.
I'd then have a sheet for each unique position type. One for a QB
(quarterback) and that would need to look at the master sheet and find
all entries where Position = QB and copy those rows to the QB sheet.
However, remember that each position only uses a subset of the overall
attributes so I wouldn't want to grab the full row. I'd only want to get
specific parts of the row relevant to each position ignoring anything
unneeded.
Then, lastly, on those position-based sheets that's where all my
calculations will be. Players are rated in each attribute on a scale of
0-99 and my approach gives players a "bonus" for being above a certain
number for each attribute but a heavy "penalty" for being below that
number.
As an example, take an offensive lineman. Strength is a key attribute
for them. To be a good lineman you must have a strength of at least 80.
My calculation for Strength for linemen might be 1 and 10. In other
words, for every point above 80 you get a point but for every point
below 80 you lose 10 points.
The point of the above is to make it clear that each positional sheet
will have lots of formulas and such on it so there's more to consider
than just dumping data from one sheet to another. The target sheet also
needs to be able to preserve the formulas that are there.
How can I automate this?
Thanks....
This is not exactly what you are after, but does carry many of the
elements, and has the benefit of being about football, AND has a good set
of team helmets to use.
http://office.microsoft.com/en-us/te...101939767.aspx
The 2009 version is better because I think it has the data filled
in,but if you fill in the game stats, the green and red zones all work
right.
Took me a few iterations to get it somewhat right. Haven't worked on
it since they messed with the template site.