Vendor Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7
Jun 8 Jun 15 Jun 22 Jun 29 Jul 6
Jul 13 Jul 20
Manhattan 346 349 351 348 340
333 328
El Paso 518 549 558 584 596
607 616
Terra H 478 484 470 485 470
506 491

Above is a sampling of my data. I am in desparate need of a calc that will
give me the average for each Vendor by Month according to the month that is
associated to Week. (there are actually 16 weeks). Every week my dates will
change. Thank you!

Hi,

Try something like this array formula:

=AVERAGE(IF(MONTH(B$2:Z$2)=6,B3:Z3,""))

copy down. This assumes that the dates are Excel dates and are on row 2
with the first vendor's data on row 3. Extend out as many columms as you
like. Array formulas are entered by pressing Shift+Ctrl+Enter, not Enter.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"drober" wrote:

Vendor Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7
Jun 8 Jun 15 Jun 22 Jun 29 Jul 6
Jul 13 Jul 20
Manhattan 346 349 351 348 340
333 328
El Paso 518 549 558 584 596
607 616
Terra H 478 484 470 485 470
506 491

Above is a sampling of my data. I am in desparate need of a calc that will
give me the average for each Vendor by Month according to the month that is
associated to Week. (there are actually 16 weeks). Every week my dates will
change. Thank you!

Shane

I tried this but I get #value. Also wont MONTH(B$2:Z$2)=6; only return June?

Thanks for your help with this, I am at a lost ; (

"Shane Devenshire" wrote:

Hi,

Try something like this array formula:

=AVERAGE(IF(MONTH(B$2:Z$2)=6,B3:Z3,"")))

copy down. This assumes that the dates are Excel dates and are on row 2
with the first vendor's data on row 3. Extend out as many columms as you
like. Array formulas are entered by pressing Shift+Ctrl+Enter, not Enter.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"drober" wrote:

Vendor Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7
Jun 8 Jun 15 Jun 22 Jun 29 Jul 6
Jul 13 Jul 20
Manhattan 346 349 351 348 340
333 328
El Paso 518 549 558 584 596
607 616
Terra H 478 484 470 485 470
506 491

Above is a sampling of my data. I am in desparate need of a calc that will
give me the average for each Vendor by Month according to the month that is
associated to Week. (there are actually 16 weeks). Every week my dates will
change. Thank you!