Hi I am trying to calculate rolling average for attached data in row currently i am using formula =IFERROR(AVERAGEIFS(DM8:GZ8,$DM$1:$GZ$1,"Mon",DM8:GZ8,"<>0"),"") to calculate average however now want to calculate in rolling for previous 16 weeks as off today. Data will be uploaded on real time basis daily and want to calculate average of previous 16 weeks of Weekdays "Sun" , "Mon"....... Can anyone please help me ASAP. Attached is Excel file for your reference Password to open is "mis"
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Calculate Rolling Average exclude zero and for Weekday
0
Answers
0
So, I hope you are good in Excel because this formula is a real big one lol.
=IFERROR(AVERAGEIFS(INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH(TODAY()-(2*7),Z2:DM2,0)+26,4),"1","")&4):INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH(TODAY(),Z2:DM2,0)+26,4),"1","")&4),INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH(TODAY()-(2*7),Z2:DM2,0)+26,4),"1","")&1):INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH(TODAY(),Z2:DM2,0)+26,4),"1","")&4),"Mon",Z4:DM4,"<>0"),"")This replaces:
=IFERROR(AVERAGEIFS(Z4:DM4,$Z$1:$DM$1,"Mon",Z4:DM4,"<>0"),"")Right now, the new formula only goes back 2 weeks from today.
Change the 2 in this part of the formula: 2*7 to however many weeks you want it to go back. 2*7 should appear in the formula twice.
I don't think you have enough cells in the table to go back 16 weeks.
Also, the 26 in the formula is important and should be the number of the column that starts the data table; currently, that is column Z.