dinsdag 9 september 2014

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
Een reactie posten