Excel - Basketball League Scoresheet

     Every spring, we run a basketball league for 19-and-under-year-old kids as one of our programs.  One of the fun things we do is keep track of the kids' points and keep track of points per game throughout the year.  We also track fouls during the game.  5 fouls and you're out of the game.  To handle this, I've created an Excel spreadsheet that I print out for us to log these stats during the game.  I love Excel.  There, I said it.  I also set up a master list of all the team rosters that is similar to the scoresheet.  Here is a snapshot of each:

Before, each week I had been copying the entire team on the Rosters sheet, switching tabs, and pasting the team into the Scoresheet. (Ex: Select all of Team 2 on the right, copy to clipboard, paste over top of Team 5 on the left for next week's game)

On the Scoresheet, I wanted to set it up to where I could just type in the team number in cell A25, for example, and it would pull the entire team roster from the Rosters sheet for me!  In order to do this, I'm going to have to set up a bunch of formulas to map to the correct row on the Rosters sheet.

On the Scoresheet, say I put "1" in A25.  Then I want B26 to grab "Mike" from Rosters - B2.

If I put "2" in A25, I want B26 to grab "Chris" in Rosters - B13.

If I put "3" in A25, I want B26 to grab "Nate" in Rosters - B24 (not shown).

If I put "4" in A25, I want B26 to grab "Drew" in Rosters - B35 (not shown).

And so on...  I need a formula that grabs a different name based on the number in A25.

1 --maps to--> 2        (as in B2)

2 --maps to--> 13      (as in B13)

3 --maps to--> 24      (as in B24)

4 --maps to--> 35      (as in B35)

5 --maps to--> 46      (as in B46)

6 --maps to--> 57      (as in B57)

7 --maps to--> 68      (as in B68)

The difference between each number on the right is 11.

Ever remember learning y - y0 = m (x - x0)?  It's the point-slope formula you learn in algebra.  We're going to need it!  The rate of change, or slope (m), is 11.
Note that the number on the right is dependent on the number on the left. y-values are dependent and x-values are independent.  So the values on the left are x-values, and the numbers on the right are our y-values.

Plug in any number and it's mapped number from that list. We'll choose the first and easiest: 1 mapping to 2

y-y0 = m(x-x0)

y - 2 = 11(x - 1)

y - 2 = 11x - 11
y - 2 + 2 = 11x - 11 + 2

y = 11x - 9

Sure enough, that's the exact formula I need to transfer player 1 to his spot automatically!

The formula part says 11 times the number in A15, then the "-10+A16" part...A16 is "1", so "-10+1" is -9...so 11x-9, just like our formula above! (I made that last part more complicated for flexibility with the formula copying and pasting etc.  If I copy this to the next line down, I want it to be 11x-9+1 or 11x-8.)

This is the same process I used throughout the whole spreadsheet to automatically pull the correct names from the Rosters based on that one itty-bitty number in that one cell!  If I change the number in that cell, all the names change on the Scoresheet for me!  Not only did I use this method here, but also in another spreadsheet where I can enter the scores for the games and all the standings and team records update for me!

This took some work, but because of it, I am literally doing 10 times less work every week on this.  It is a breeze now.  I didn't have to do this, but this knowledge and skill saved me time here and will in the future, too... on this and other useful sheets!

What you learn in class that applies:
1. Rate of change or slope
2. Mapping x-values to y-values
3. Point-slope formula
4. Dependent (y) and independent (x) variables
5. Balancing an equation (adding 2 to both sides)
6. Distributive property (multiplying 11 into the x and 1)