My first choice would be a pivot table as already mentioned. Grouping, sub-totals and averages are what they are good for. The date column has to be actual dates, not just text that looks like dates to make the grouping work. A second option is a helper column with a formula that looks like this: =DATE(YEAR(A1),MONTH(A1),1) What that does is converts every date that falls within the month to the first day. In your report section, you'd have a list of possible months. The first one would be 1/1/2004. The second row would have a formula that you can copy down: =DATE(YEAR(B1),MONTH(B1)+1,1) This will magically give you a list of months in the column. Beside that list, you will need a formula that is similar to one I used in a previous post. =IFERROR(SUMIFS(TablePriceData[Price],TablePriceData[Month],C2)/COUNTIFS(TablePriceData[Month],C2),NA()) Oh, you might as well look at the sample I built to get an idea. I threw in a chart for the fun of it, just because the data was already there. EDIT: changed the spreadsheet to use AVERAGEIFS instead of SUMIFS/COUNTIFS.
Edited by Omar 9/16/2014 21:17
Attachments ---------------- Average by Month.xlsx (16KB - 168 downloads)
|