Currently I am using a formula to calculate the last 7 days average to then use that average to project data as a run rate for the remaining days of the month. My current method is hindered due to if there is a slowdown in business (ie. Holiday, shutdown, etc.) the run rate will skew heavily, accordingly. I would like to adjust my averageifs formula [currently: =IFERROR(AVERAGEIFS($I33:$BR33,$I$24:$BR$24,1),"")] to include an additional plug that will reference the same range, but of row 21 ($I$21:$BR$21) to replace a day in the average with a more normalized business day (possibly from the week prior--using the same weekday) so the run rate will calculate more accurately and smooth out any large fluctuations in projections. For additional context, row 21 has an indicator (if(vlookup) to determine if that specific day is an abnormal business day (holiday, etc..) which would be triggered by ('1') if an abnormal business day and ("") if normal.
Apologies for the lengthy explanation and any help/suggestions are greatly appreciated!
Apologies for the lengthy explanation and any help/suggestions are greatly appreciated!
