J
jayhawk1919
I know the title is a bit confusing, and the concept may be hard to
explain, but if I could do this it would save me hours, maybe even days
of time.
I would appreciate the help.
I'm making a spreadsheet that takes a basketball player's statistics in
real life, and turns them into a straight number using a formula I
developed. (It is for a fantasy basketball league I run, where the
players score different amount of points in our league depending on
what offense they run. But you probably don't need to know that.)
For example, if the team decides to run a Fast Break offense, the
formula I have developed reads like so: (POINTS * 1.5) + (REBOUNDS
*0.5) + (ASSISTS*3) + BLOCKS + STEALS - (TURNOVERS *2).
So, at the top of my spreadsheet I have a table with all the players on
a certain NBA team, and a table that just holds values I will input.
The reason the players are grouped by NBA team is that when I read a
box score to input the players real life statistics, it's easier to do
it team by team than to switch between NBA teams and go player by
player. (If you need clarification on this, just ask, but it doesn't
really matter that much I don't think.) So the top of the spreadsheet
looks like so:
[image: http://mywebpage.netscape.com/FFSBasketball/TopTablesBig.jpg]
Which zoomed in is:
[image: http://mywebpage.netscape.com/FFSBasketball/TopTablesZoom.jpg]
Then after every team is set up with a table like that, I then have the
rosters of the teams in my league set up at the bottom, to reference to
the values entered at the top. Here is a picture of how that looks:
[image:
http://mywebpage.netscape.com/FFSBasketball/RosterExplanation.jpg]
Here are the key points in this picture. First, the red wording was
put in by me as explanation.
At the top left as you can see is the name of the team in my league.
The column with "PG, SG, SF, etc.", is headed by "Position", because
that is the position on the court that the player is playing on his
team. (You can ignore this, it does have to do with what I'm doing, but
once I figure out how to do it, I can apply it to different
positions.)
The next column has the players' name.
And this next column is the column that really matters. In this column
I put in the row that that players statistics are located on at the top
of the sheet. If you look back at the picture of the NBA Teams'
roster, you will see that each player is located on a certain row
(obviously, that's how Excel works). So for example, Gilbert Arenas'
actual statistics, when I type in the box score at the top of the
sheet, are in row 119 of the spreadsheet.
The next columns are just what offense they run, and they contain the
following formula. However this formula changes depending on what
offense and what position the player plays.
Here is what is in the Superstar offense for Gilbert Arenas:
[image:
http://mywebpage.netscape.com/FFSBasketball/FormulaExample.jpg]
Now in this example, Arenas has the number of points he scored in his
NBA game divided by 2. His NBA points will be located in cell C119
once I input the box scores. The next numbers are how his rebounds,
assists, blocks, steals and turnovers will be modified. All point
guards running the shootout offense have this same thing.
And here is my problem, and what I want to do.
Instead of having to type that player's unique row number into every
single cell, is there a way to reference to one cell depending on
another cell's value.
Let me elaborate. Is there a way to make Gilbert Arenas' formula such
that instead of typing in 119 for all of Arenas' formulas, I could say
I just want it to reference to cell D496 (which is where Arenas' row
number is contained on the spreadsheet) take that value and then use
that to determine the row number.
See this is where the wording is hard. What I want to do is, instead
of cell C119 I want it to say cell C(=D496). When it reads cell D496,
it would find the value 119 there. Then the value inside the
parentheses would be 119, and Excel could figure out that it wants to
find C119.
What I'm doing is creating my own function using the Visual Basic
editor, so I could say =PGShootout(D496). Then the function I created
would contain the formula I needed, accepting the argument "RowNumber".
Arenas' row number is contained in D496, as I said before, PGShootout
would read D496, find that it's value is 119, then in the function
PGShootout I could say "Multiply Column C, Row (Row Number) by 0.5" (of
course in programming language, though). So it would look for
RowNumber, which in the Arenas case is D496, whose value is 119, and
figure out that it needs to multiply column C, Row 119 by 0.5.
I tried to explain myself as best as possible here, but it still might
be unclear to some. If you have any questions, please ask.
I could REALLY use the help, and I unfortunately have a deadline coming
up very soon on this, as October 28th is the start of the NBA season.
Thanks in advance.
explain, but if I could do this it would save me hours, maybe even days
of time.
I would appreciate the help.
I'm making a spreadsheet that takes a basketball player's statistics in
real life, and turns them into a straight number using a formula I
developed. (It is for a fantasy basketball league I run, where the
players score different amount of points in our league depending on
what offense they run. But you probably don't need to know that.)
For example, if the team decides to run a Fast Break offense, the
formula I have developed reads like so: (POINTS * 1.5) + (REBOUNDS
*0.5) + (ASSISTS*3) + BLOCKS + STEALS - (TURNOVERS *2).
So, at the top of my spreadsheet I have a table with all the players on
a certain NBA team, and a table that just holds values I will input.
The reason the players are grouped by NBA team is that when I read a
box score to input the players real life statistics, it's easier to do
it team by team than to switch between NBA teams and go player by
player. (If you need clarification on this, just ask, but it doesn't
really matter that much I don't think.) So the top of the spreadsheet
looks like so:
[image: http://mywebpage.netscape.com/FFSBasketball/TopTablesBig.jpg]
Which zoomed in is:
[image: http://mywebpage.netscape.com/FFSBasketball/TopTablesZoom.jpg]
Then after every team is set up with a table like that, I then have the
rosters of the teams in my league set up at the bottom, to reference to
the values entered at the top. Here is a picture of how that looks:
[image:
http://mywebpage.netscape.com/FFSBasketball/RosterExplanation.jpg]
Here are the key points in this picture. First, the red wording was
put in by me as explanation.
At the top left as you can see is the name of the team in my league.
The column with "PG, SG, SF, etc.", is headed by "Position", because
that is the position on the court that the player is playing on his
team. (You can ignore this, it does have to do with what I'm doing, but
once I figure out how to do it, I can apply it to different
positions.)
The next column has the players' name.
And this next column is the column that really matters. In this column
I put in the row that that players statistics are located on at the top
of the sheet. If you look back at the picture of the NBA Teams'
roster, you will see that each player is located on a certain row
(obviously, that's how Excel works). So for example, Gilbert Arenas'
actual statistics, when I type in the box score at the top of the
sheet, are in row 119 of the spreadsheet.
The next columns are just what offense they run, and they contain the
following formula. However this formula changes depending on what
offense and what position the player plays.
Here is what is in the Superstar offense for Gilbert Arenas:
[image:
http://mywebpage.netscape.com/FFSBasketball/FormulaExample.jpg]
Now in this example, Arenas has the number of points he scored in his
NBA game divided by 2. His NBA points will be located in cell C119
once I input the box scores. The next numbers are how his rebounds,
assists, blocks, steals and turnovers will be modified. All point
guards running the shootout offense have this same thing.
And here is my problem, and what I want to do.
Instead of having to type that player's unique row number into every
single cell, is there a way to reference to one cell depending on
another cell's value.
Let me elaborate. Is there a way to make Gilbert Arenas' formula such
that instead of typing in 119 for all of Arenas' formulas, I could say
I just want it to reference to cell D496 (which is where Arenas' row
number is contained on the spreadsheet) take that value and then use
that to determine the row number.
See this is where the wording is hard. What I want to do is, instead
of cell C119 I want it to say cell C(=D496). When it reads cell D496,
it would find the value 119 there. Then the value inside the
parentheses would be 119, and Excel could figure out that it wants to
find C119.
What I'm doing is creating my own function using the Visual Basic
editor, so I could say =PGShootout(D496). Then the function I created
would contain the formula I needed, accepting the argument "RowNumber".
Arenas' row number is contained in D496, as I said before, PGShootout
would read D496, find that it's value is 119, then in the function
PGShootout I could say "Multiply Column C, Row (Row Number) by 0.5" (of
course in programming language, though). So it would look for
RowNumber, which in the Arenas case is D496, whose value is 119, and
figure out that it needs to multiply column C, Row 119 by 0.5.
I tried to explain myself as best as possible here, but it still might
be unclear to some. If you have any questions, please ask.
I could REALLY use the help, and I unfortunately have a deadline coming
up very soon on this, as October 28th is the start of the NBA season.
Thanks in advance.