Calculate monthly average from daily data without PivotTable

Question

I have two columns on my worksheet: column A has the date in the format dd/mm/yyyy and column B has the daily information. Without using a pivot table, I must calculate the monthly average for each month using the daily data I have. There is absolutely no data on several of the days. I am unable to use a pivot table because I must use the monthly average to subsequently spot outliers in the daily data.

It's crucial for this example that I am able to generate the monthly average data for any month and year in my list without using a PivotTable. Excel formulas or VBA might be used as the answer.

Answer 1

Assuming you have the months in column D enter this formula in E2 and copy till last month.

=AVERAGEIFS($B$1:$B$62,$A$1:$A$62,">="&D2,$A$1:$A$62,"<="&EOMONTH(D2,0))