Hi, I am trying to figure out why my formula isn't working. I am trying to figure out the rolling 3 weeks based on a given week. I am using the following date 4/22/2013 and It is working in one section of my workbook but not another. Below is the formula.
SUMPRODUCT(--('Avg Daily Rev Pivot'!$B$11:$B$953>=(MAX(IF(WEEKDAY('Avg Daily Rev Pivot'!$B$11:$B$953=WEEKDAY($D$3)),'Avg Daily Rev Pivot'!$B$11:$B$953))-21)),--((WEEKDAY('Avg Daily Rev Pivot'!$B$11:$B$953)=WEEKDAY($D$3))),--('Avg Daily Rev Pivot'!$L$11:$L$953))/3
I am attaching a small dataset example of what i am trying to do. I want it to find what the average 3 weeks Monday is for the same timeframe Last Year. I also need to adjust this formula to only look at the average for Jan - April, not the entire year.
Let me know if this makes sense. I really appreciate anyone's help.
Bookmarks