I have several teams. Each has it's own spreadsheet.
On each spreadsheet has the schedule broken down into week 1, week 2, etc. Some weeks are "BYE" weeks. Col. A has the week number (1,2,3, etc.). Col B. has the team it's playing. If Col. B contains "BYE" I want to exclude that value from the average. Now, here comes the tricky part... Each team plays 8 games for 9 weeks. I need to know the averages up to a certain week. The value of the week I want to calculate up to will be in a cell (D2) For examlple D2 = 4 - meaning up to week 4.

WEEKS = A2 through A10
Teams = B2 through B10 (one of these weeks will have "BYE")
Amount = C2 through C10
Weeks to calculate = D2

Need to know the average of C2 through C10 up to the number of weeks in D2 not include "BYE" weeks.

Thanks for your help.