try
=IFERROR(IF($A14="","",if(MATCH('Executive Summary'!$H$9,$AN$3:$BG$3) -$AK$1>0,AVERAGE(OFFSET($AN14,0,MATCH('Executive Summary'!$H$9,$AN$3:$BG$3) -$AK$1,1,AK$1))),0),0)
try
=IFERROR(IF($A14="","",if(MATCH('Executive Summary'!$H$9,$AN$3:$BG$3) -$AK$1>0,AVERAGE(OFFSET($AN14,0,MATCH('Executive Summary'!$H$9,$AN$3:$BG$3) -$AK$1,1,AK$1))),0),0)
Cheers !
Excel Range to BBCode Table
Use Social Networking Tools If You Like the Answers !
Message to Cross Posters
@ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)
GENIUS. IT WORKS! I am over the moon. Thanks so much. Question, if the May 6 date was picked, the result shows "FALSE"....Is there any way that I can have it show "N/A" instead? This will be displayed on a scorecard....and "false" looks odd. Thanks so much again.
One last question, the end result is off by one week. For example, if 9/16, the results starts counting from 9/16 and 4 previous weeks (which is 8/26, 9/2, 9/9, 9/16). Is there a way where the formula would actually start to count the week before? So if someone chose 9/16, the rolling 4 week average would include weeks of 8/18, 8/26, 9/2, 9/9? It would not include the 9/16 results...make sense?
Sorry if I am making it a little more complicated, but the original formula above did this....Thank you in advance for your help. I TRULY appreciate this.
Posting Permissions
Reply With Quote
Bookmarks