G
Guest
I'm sure this is easy, I just can't find it.
I'm using Excel 2003 as an analytical tool for my fantasy baseball league.
It's proving very helpful, but I want to be able to automate the scoring part
of the process.
Background on the scoring procedure:
In fantasy baseball 5x5 roto league scoring, each team is ranked in 10
categories (baseball stats - batting average, home runs, ERA, etc.) I have
the raw data (projected stats for 2005) on each player, and the rosters for
all the teams, which has allowed me to project each team's total is each stat.
There are 10 teams, and in each stat each team will receive 1-10 points,
with the team that is best in that stat getting a 10, second getting a 9,
etc. In most stats a high number is better but in some (e.g. ERA) lower is
better. In case of a tie the point is split - e.g. if 2 teams tie for first
in ERA with a 3.10 and the third place team has a 3.30, teams 1 and 2 each
get 9.5 points, and team 3 gets 8.
So my worksheet has a 10x10 grid - 10 rows representing teams, 10 columns
representing stats. I need to be able to translate this into another 10x10
grid where each cell from the first (representing a baseball stat) is
translated to a number from 1-10 in the second representing a score. Column
11 will be a sum of the previous 10 columns (the team's total score) and
ideally the whole thing can be sorted by this score.
What I need is a function to look at the list of 10 scores, and without
changing their positions on the sheet (since there are 10 independent
categories I can't just use Sort) assign the number from 1 to 10 and put it
in another location.
Any of you who have played fantasy ball will see that this is potentially a
very powerful tool - all I'll need to do to analyze a trade is swap the
player stats, hit F9 and look at the effect on the team stats and the total
scores.
I'm using Excel 2003 as an analytical tool for my fantasy baseball league.
It's proving very helpful, but I want to be able to automate the scoring part
of the process.
Background on the scoring procedure:
In fantasy baseball 5x5 roto league scoring, each team is ranked in 10
categories (baseball stats - batting average, home runs, ERA, etc.) I have
the raw data (projected stats for 2005) on each player, and the rosters for
all the teams, which has allowed me to project each team's total is each stat.
There are 10 teams, and in each stat each team will receive 1-10 points,
with the team that is best in that stat getting a 10, second getting a 9,
etc. In most stats a high number is better but in some (e.g. ERA) lower is
better. In case of a tie the point is split - e.g. if 2 teams tie for first
in ERA with a 3.10 and the third place team has a 3.30, teams 1 and 2 each
get 9.5 points, and team 3 gets 8.
So my worksheet has a 10x10 grid - 10 rows representing teams, 10 columns
representing stats. I need to be able to translate this into another 10x10
grid where each cell from the first (representing a baseball stat) is
translated to a number from 1-10 in the second representing a score. Column
11 will be a sum of the previous 10 columns (the team's total score) and
ideally the whole thing can be sorted by this score.
What I need is a function to look at the list of 10 scores, and without
changing their positions on the sheet (since there are 10 independent
categories I can't just use Sort) assign the number from 1 to 10 and put it
in another location.
Any of you who have played fantasy ball will see that this is potentially a
very powerful tool - all I'll need to do to analyze a trade is swap the
player stats, hit F9 and look at the effect on the team stats and the total
scores.