From Day To Month: Calculating the average "trend" for the month

0
votes

I have a table called DayTable that contains Day with a corresponding value, Trend. I have created a second table MonthTable with column Month and an empty column Trend. In each cell of the column Trend, I would like to calculate the average trend value for that month.

My initial plan was to create a formula that averages the Trend value if the month and year of the value in the Month column of the MonthTable equals the month and year of each day in the DayTable.

=AVERAGEIFS(DayTable[Trend],MONTH(DayTable[Day]),">="&MONTH([@Month]),YEAR(DayTable[Day]),">="&YEAR([@Month]))

This is my most recent formula that does not work.

I am interested if anyone has insight into making this formula work or could suggest a better one.

1
excel-formulaaverage

1 Answers

0
votes

I think you should reconsider the structure of your data.

  • I would have only ONE table with 2 main data series: DATE and TREND.
  • Add a calculated column for MONTH, you can use =TEXT(cell,"mmm-yyyy")
  • Summarize using Pivot.

Something like this: new table

Hope this helps.