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
Bookmarks