Assume a simple two column dataset with dates in column A and numbers in column B. The dates in column A are from January 1, 2013 to December 31, 2016 and numbers in column B are for the period January 1, 2013 to December 31, 2015 (there are no numbers for January 1, 2016 to December 31, 2016).
The objective is to “Compute an average for each day of calendar year 2016. The average should be for the occurrence of that day in the previous 3 years”. Here’s an example:
1. January 1, 2016 was a Friday (the first Friday of 2016) and is in cell A1097
2. In cell B1097, the average should be computed as: Average of the “First Friday of each of the previous 3 years”
3. January 8, 2016 was a Friday (the second Friday of 2016) and is in cell A1104
4. In cell B1104, the average should be computed as: Average of the “Second Friday of each of the previous 3 years”
I have solved this problem with the help of the PowerPivot. You may refer to my solution in this workbook.
Compute an average for the same day in the past 3 years
{ 4 Comments }