Making a spreadsheet dynamic

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