Excel: nice example of the formula EOMONTH

Got a question about calculating an average for monthly data. Here is an example of the data starting in cell A1:

date	dieselprice	avgpermonth
1-1-2007	0,985	0,96355
2-1-2007	0,985	0,96355
3-1-2007	0,984	0,96355
4-1-2007	0,984	0,96355
5-1-2007	0,978	0,96355
6-1-2007	0,964	0,96355
7-1-2007	0,964	0,96355
8-1-2007	0,964	0,96355
9-1-2007	0,964	0,96355
10-1-2007	0,964	0,96355
11-1-2007	0,964	0,96355
12-1-2007	0,958	0,96355
13-1-2007	0,958	0,96355
14-1-2007	0,958	0,96355
15-1-2007	0,958	0,96355
16-1-2007	0,954	0,96355
17-1-2007	0,954	0,96355
18-1-2007	0,984	0,96355
19-1-2007	0,948	0,96355
20-1-2007	0,948	0,96355
21-1-2007	0,948	0,96355
22-1-2007	0,948	0,96355
23-1-2007	0,948	0,96355
24-1-2007	0,958	0,96355
25-1-2007	0,964	0,96355
26-1-2007	0,964	0,96355
27-1-2007	0,964	0,96355
28-1-2007	0,964	0,96355
29-1-2007	0,964	0,96355
30-1-2007	0,964	0,96355
31-1-2007	0,964	0,96355
1-2-2007	0,964	0,98239
2-2-2007	0,964	0,98239
3-2-2007	0,978	0,98239
4-2-2007	0,978	0,98239
5-2-2007	0,978	0,98239
6-2-2007	0,976	0,98239
7-2-2007	0,992	0,98239
8-2-2007	0,992	0,98239
9-2-2007	0,982	0,98239
10-2-2007	0,982	0,98239
11-2-2007	0,982	0,98239
12-2-2007	0,982	0,98239
13-2-2007	0,993	0,98239
14-2-2007	0,983	0,98239
15-2-2007	0,983	0,98239
16-2-2007	0,983	0,98239
17-2-2007	0,973	0,98239
18-2-2007	0,973	0,98239
19-2-2007	0,973	0,98239
20-2-2007	0,974	0,98239
21-2-2007	0,974	0,98239
22-2-2007	0,974	0,98239
23-2-2007	0,984	0,98239
24-2-2007	0,994	0,98239
25-2-2007	0,994	0,98239
26-2-2007	0,994	0,98239
27-2-2007	1,004	0,98239
28-2-2007	1,004	0,98239

Of course, I could have used a formula like 

=AVERAGE($C$2:$C$32)

and change that every time it is needed. In stead I wanted to use a formula which calculates the average for each month automatically, depending on the row it is in.

I ended up with this one, from cell C2 down:

=AVERAGE(INDIRECT(ADDRESS(ROW()-DAY(A2)+1;2)&":"&ADDRESS(ROW()+DAY(EOMONTH(A2;0))-DAY(A2);2)))

This formula works fine for me. Just wondering wether it can be done more easy.

On a second worksheet I created:

	end of month	avg per   month
0	31-1-2007	0,964
1	28-2-2007	0,982
2	31-3-2007	1,000
3	30-4-2007	1,024
4	31-5-2007	1,026
5	30-6-2007	1,049
6	31-7-2007	1,058
7	31-8-2007	1,046
8	30-9-2007	1,077
9	31-10-2007	1,091
10	30-11-2007	1,180
11	31-12-2007	1,144

In cell B2 I put the formula:

=EOMONTH(prices!$A$2;A2)

and copied it downwards.

In cell C2 I put the formula:

=VLOOKUP(B2;prices!A:C;3;FALSE)

in order to  look up the right average on the first sheet.

Thanks to +Brian Canes and +Shane Devenshire we can even replace the formula in C2:

=AVERAGEIFS(prices!B$2:B$3660;prices!A$2:A$3660;">"&EOMONTH(B2;-1);prices!A$2:A$3660;"<="&EOMONTH(B2;0))

If we use the last formula we don't even need the third helper column on the first sheet anymore.

You can download the file ExcelExampleEOmonth.xlsx through

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing