Average of Data occurring on same date each year

[jlcoombs]

I have a set of data spanning a number of years.  I want to chart the average of the value for the same day for the past x years.

ie.

Day	Value
1 Jan 2013	5
2 Jan 2013	6
3 Jan 2013	7
1 Jan 2014	1
2 Jan 2014	2
3 Jan 2014	3
1 Jan 2015	4
2 Jan 2015	5
3 Jan 2015	6

So I want to be able to plot 2015 data which is easy to extract, but then I want to be able to plot the average for that day from the past 2 years alongside. i.e. the result set becomes

Header 1	2015	Average Last 2 Years
1 January	4	3=(5+1)/2
2 January	5	4=(6+2)/2
3 January	6	5=(7+3)/2

What I'm trying to do is similar to how they report what the average temperature on this day has been over the last 10 years or so, the average is not the last 10 days, its the average for that same day of the year for the past 10 years.

Can someone assist with how to do an appropriate average or range function to get the required results.  Your assistance is much appreciated.

Jamie

[Anonymous]

Hi Jamie,

Please see attached example.

I create the partial date (Day & Month) in the script and then use Set Analysis in the table to pick the appropriate years to calculate based on Today's date. Since it's 2015 it will use 2015 for the first column and 2013-2014 for the average as per your example above. !