# AVERAGEIF - calculate monthly average

1. ## AVERAGEIF - calculate monthly average

In column A I have the date in dd/mm/yyyy format (e.g. 20/01/2000) with 365 rows (one for every day of the year) and in column B I have "widgets produced per day", one value for every day of the year.

I am trying to work out what the monthly average widgets produced is but don't want to have to use the AVERAGE function and then manually select the range corresponding to each month of the year. I've been trying to use the AVERAGEIF function in order to group the days into months before taking the average but can't get the correct syntax, e.g.

{=AVERAGEIF(B1:B366, MONTH(A1:A366)=1)}

is supposed to only take the average if MONTH = 1 (January) but it doesn't work.

I've also tried inserting a 'helper column' in column B that reports the current month, e.g. in B1: =MONTH(A1)

and then the AVERAGEIF formula is:

{=AVERAGEIF(C1:C366, B1:B366=1)}

but this doesn't work either. Can anyone offer any help? The ideal solution would be to not need helper columns and to keep the whole formula within one cell.

Regards
Rob  Register To Reply

2. ## Re: AVERAGEIF - calculate monthly average

=AVERAGE(IF(month(B1:B366)=1,C1:C366))

Confirm COntrol+Shift+Enter
The AVEARGEIF() function does not work on array so you can not use MONTH() unless you have got AVERAGEIFS() where you can specify the beginning and the end of the month.  Register To Reply