Calculating Cell Averages Based upon conditions

[Kfetterman1]

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.

[stevebriz]

If can post you xls this would help us work out what goes where and then we can help you write a macro

[Kfetterman1]

I'm just starting this XLS. So, this is small example what I want. The average is based upon the number of weeks in B2. I'm going to pull an amount into B2 from another worksheet. Each team will have it's own worksheet that looks similar to this one...


Attached is a example XLS that I like to do.