Making a spreadsheet dynamic

Hello,

This asking a lot, I know, and I will honestly be surprised if anyone is following me. I decided to explain why this was a problem first before asking for help because I thought it might better facilitate someone in coming up with a solution. If any potential problem solvers just want to skip to the nitty gritty of the problem, just scroll down and click the last image.

I have an interesting Excel problem and I just can't seem to figure it out. So my situation is that I like to play in fantasy basketball leagues. The way Fantasy Basketball works is that you draft a team of actual NBA players, and you try to pick a team that finishes highest in all of the league's scoring categories, which are typically points, rebounds, assists, steals, blocks, 3-pointers made, field goal percentage (FG%), and free throw percentage (FT%).

Now points, rebounds, steals, blocks, and 3s are pretty self-explanatory - but it gets tricky when you rate percentage players. For example, the average NBA player shoots 46% from the field. LeBron James shoots 49%, and a lesser known player who shoots much less than LeBron does named Ronnie Turiaf shoots 51% from the field. So conventional wisdom would say that Turiaf is the better player to have to improve your team's FG% since he shoots a higher percentage. Not necessarily! Since you have to play 11 other players, on average in a 20-team league 11 players will shoot 54-117 from the field, 46%. Adding Ronnie Turiaf's average of 2.4 shots per 4.6 attempts per game will get your team up to just 46.4% shooting. However, adding LeBron's 9.7 makes per 19.9 shots will get your team's percentage up to 46.6%, so even though LeBron shoots a worse percentage than Turiaf his positive impact in the category is greater.

OK - So now on to my Excel question. In a larger league where more NBA players have to be rostered, the effect of one guy like LeBron who takes a huge amount of shots is greater than in a smaller league where only high quality NBA players who also take a lot of shots are rostered. What I am trying to do is create a dynamic spreadsheet that can use different baselines for leagues of different sizes.

http://i301.photobucket.com/albums/n...ingsFolder.jpg

For the cells titled "Rounds of Draft" and "Teams in League" - multiplying those 2 numbers gives you the number of players rostered in your league.

http://i301.photobucket.com/albums/n...layerstats.jpg

The above image shows my spreadsheet with all of the players' stats. So, for the league settings I have entered in my first picture, 12-team league with 13 rounds of the draft, 12*13=156, so for the field goals made value of a generic player I'd want the average FGM of the highest rated 156 players on my list. However, if I were playing in a 20-team league with 13 rounds, a total of 260 NBA players would be rostered, so for the generic players in my first image I'd want their FGM and FGA to be the average for the top 260 players, and then I could enter in LeBron James or Ronnie Turiaf and see what type of impact they'd have in the category given leagues of varying sizes.

SO - After all that Intro - FINALLY - My true Excel question!! (I apologize!!)

What I want to do is create a sheet that will set these baselines for me and all I have to do is type in the number of teams and the number of rounds of the draft. I tried to explain EXACTLY what i'm trying to do in this final image:

http://i301.photobucket.com/albums/n...celProblem.jpg

Can anyone help me out? I'd be very appreciate. Thanks for reading and for trying.

bump! Is this the right forum for this?

Thanks for the welcome and suggestion SHG. I did read the forum rules and was in the middle of typing out a new post when the solution hit me, so I guess what I'll do is upload the Excel sheet here with both the question and the solution in case anyone else has had this problem.

If you take a look at the attached sheet (or the images in the original post) you can see that I was trying to get the average for a specific range of numbers, with the size of the range depending on the sum of two numbers. Specifically, i wanted the average amount of baskets made by the number of rosterable players in my fantasy league.

I tried all sorts of things to get this to work. Specifically, I:

1) Attempted to record a macro where I did the calculation of (teams in league*rounds of draft), then copied that sum and wrote out the equation for average FGM and pasted in the sum, but when I ran the macro with different values for teams in league or rounds of draft, the macro would always paste the original sum from when I first recorded the macro into the equation.

2) I considered entering a bunch of nested IF functions. I was willing to enter 250 or so if functions in order to make this work properly, even though it would've been awful. But I read that you can only next 7 IF functions.

3) I considered trying to learn the CHOOSE function which I have never used before, and figured I could create ranges (I hear you can have up to 29 values in a CHOOSE function. I figured that if this worked, dividing 260 or so draftable players into 29 groups would be accurate enough.

But I was still hoping for something better. Then, I thought to use vlookup. So I added a column into my "DATA" tab on my spreadsheet called "AVGFGM" (column AE) and then added a column in the beginning numbered 1-259. I then went back to my settings tab and ran a vlookup for the sum of the rounds in draft*teams in league back to my DATA tab, and just pulled the average right from that new column. Problem solved! I am pretty happy with it.

Thanks and I'm sure I'll have more problems down the road, will be sure to follow the rules next time