Help with Average formula

  • Thread starter Thread starter GoBucks
  • Start date Start date
G

GoBucks

I am looking for help for a formula to get an average based on criteria. In
the table below there are %'s assigned to each week end date.

sheet1 (weekly values)

Name 4/1 4/10 4/17 4/24 5/1
Name 1 100 50 40 80 80
Name 2 100 100 100 50 100


sheet2 (AVG by month)
Apr-09 May-09
Name 1 xx xx
Name 2 xx xx

In sheet2 I want to calculate the average of all %'s by month per Name. For
example the average for Name 1 in Apr-09 should equal = 67.5%; Name 2 =
87.5%. Was looking for a dynamic formula that will match and avg the weeks
for a corresponding month in a column header. Any help is very much
appreciated!
 
Assuming your data in sheet 1 A1:IV100

Sheet 2

In B2:
=AVERAGE(IF((Sheet1!$A$2:$A$100=$A2)*(MONTH(Sheet1!$B$1:$IV$1)=MONTH(B$1)),Sheet1!$B$2:$IV$100))
 
Back
Top