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
