CODEWITHSUNDEEP
excelexcel-formulavba
ssteinec
ssteinec

Reputation: 43

Calculate monthly average from daily data without PivotTable

My worksheet contains two columns: column A contains the date in the format dd/mm/yyyy and column B contains the daily data. From the daily data I have to generate the monthly average for each month without a PivotTable. Some of the days contain no data at all. I cannot use a PivotTable as I have to use the monthly average to identify outliers in the daily data at a later stage.

For this example it's important that I don't use a PivotTable and that I can generate the monthly average data for any month and year in my list. The solution can be an Excel formula or VBA.

Upvotes: 2

Views: 44161

Answers (4)

Rikus Khan
Rikus Khan

Reputation: 55

Given a single date you can calculate both the End of the Month and the First of the Month for that date. Assume any given date is in E4:

For the End of the Month

=EOMONTH(E4,0)

For the First of the Month

=EOMONTH(EDATE(E4,-1),0)+1

If you place a date, any date, of the month you want to review into cell E4 the two above code snipets will give you and last and first days of the month. EDATE allows you to add and subtract whole numbers to change the month. We take one month away from the current month, found within the date in cell E4, and then find the end of the month and add a one to it to become the first day of the next month. It will work with any date. The below example shows grade averages over a month referencing any given date within the month.

Pic of the above Excel example

=AVERAGEIFS(C2:C11, B2:B11,">=" & EOMONTH(EDATE(E4,-1),0)+1, B2:B11,"<=" &EOMONTH(E4,0), A2:A11, E2)

Upvotes: 1

gerdami
gerdami

Reputation: 23

In case Daily Data contains some #N/A, one can add a additional criteria to skip non number data:

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

Upvotes: 0

PankajR
PankajR

Reputation: 407

Some of the days contain no data at all.

In case this means that there are no row for some dates and you want to calculate average based on full month than you can use the below formula:

Continuing with example given by EEM

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

Upvotes: 0

EEM
EEM

Reputation: 6659

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

enter image description here

Upvotes: 7

Related Questions