Calculate Rolling Average exclude zero and for Weekday

0

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"

Discussion

Hello Does anyone have any answer
Srushtiar Aug 23, '16 at 9:52 am

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.