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))

"GoBucks" wrote:

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!

GoBucks wrote:
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!

Not your average Average formula!

This solution may be a little simplistic. It assumes the name list is
the same in both worksheets and the dates do not overlap in years.

On Sheet2!B2 (fill right and down) this array* formula works:
=AVERAGE(IF(MONTH(B$1)=MONTH(Sheet1!$B$1:$F$1),She et1!$B2:$F2))

*Commit an array formula by pressing Ctrl+Shift+Enter, not just Enter.

Adding contingencies for multiple years and random lists of names can be
done at the expense of making the formula much more complicated.

Alternatively, were your data laid out like this:

Name Date Value
Name 1 01-Apr 100
Name 1 10-Apr 50
Name 1 17-Apr 40
Name 1 24-Apr 80
Name 1 01-May 80
Name 2 01-Apr 100
Name 2 10-Apr 100
Name 2 17-Apr 100
Name 2 24-Apr 50
Name 2 01-May 100

You could use a pivot table to average Value while automagically
grouping the time series by month. This will handle irregular name lists
as well as any dates.

TM and smafrtin..thank you very much!! Both solutions work great!!!!

"smartin" wrote:

GoBucks wrote:
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!

Not your average Average formula!

This solution may be a little simplistic. It assumes the name list is
the same in both worksheets and the dates do not overlap in years.

On Sheet2!B2 (fill right and down) this array* formula works:
=AVERAGE(IF(MONTH(B$1)=MONTH(Sheet1!$B$1:$F$1),She et1!$B2:$F2))

*Commit an array formula by pressing Ctrl+Shift+Enter, not just Enter.

Adding contingencies for multiple years and random lists of names can be
done at the expense of making the formula much more complicated.

Alternatively, were your data laid out like this:

Name Date Value
Name 1 01-Apr 100
Name 1 10-Apr 50
Name 1 17-Apr 40
Name 1 24-Apr 80
Name 1 01-May 80
Name 2 01-Apr 100
Name 2 10-Apr 100
Name 2 17-Apr 100
Name 2 24-Apr 50
Name 2 01-May 100

You could use a pivot table to average Value while automagically
grouping the time series by month. This will handle irregular name lists
as well as any dates.

You're Welcome!


"GoBucks" wrote:

TM and smafrtin..thank you very much!! Both solutions work great!!!!

"smartin" wrote:

GoBucks wrote:
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!

Not your average Average formula!

This solution may be a little simplistic. It assumes the name list is
the same in both worksheets and the dates do not overlap in years.

On Sheet2!B2 (fill right and down) this array* formula works:
=AVERAGE(IF(MONTH(B$1)=MONTH(Sheet1!$B$1:$F$1),She et1!$B2:$F2))

*Commit an array formula by pressing Ctrl+Shift+Enter, not just Enter.

Adding contingencies for multiple years and random lists of names can be
done at the expense of making the formula much more complicated.

Alternatively, were your data laid out like this:

Name Date Value
Name 1 01-Apr 100
Name 1 10-Apr 50
Name 1 17-Apr 40
Name 1 24-Apr 80
Name 1 01-May 80
Name 2 01-Apr 100
Name 2 10-Apr 100
Name 2 17-Apr 100
Name 2 24-Apr 50
Name 2 01-May 100

You could use a pivot table to average Value while automagically
grouping the time series by month. This will handle irregular name lists
as well as any dates.