Average of 5 specific cells inclusive of active row the 2 above/2 below skipping blanks rather than including in the range

Question

My Excel skills are relatively limited so am unsure if this can be done with a formula but I am hopeful it can as my Macro skills go as far a recording a Macro and making basic edits.

Basically I have an annual table, inclusive of December from the previous year and January of the following year. The table linked here is a snapshot of what it would look like, 2-1-2 daily price average , the Date column is column A.

Essentially I need to enter a 5 day average from the daily average column across 5 potentially non-contiguous days. I need to skip the blank cells i.e. Saturday & Sunday entirely and not include them in any range, but here is where I'm having trouble it also needs to skip certain weekdays ie public holidays where prices aren't published. I also need to be able to copy/drag it to all cells in the column for example December 2017-January 2019 on a rolling annual basis accounting for public holidays that will change each year or be able to make simple edits each year.

I have entered a nested If statement that returns a 1 for anything that isn't Saturday, Sunday or Public Holiday and 0 for everything that is. With limited knowledge and the fact I couldn't find something simple to exclude blanks from the Daily Average column this was/is my most hopeful option but I can't find the exact formula I need that will average a cell(s) for the active row, and the first 2 above and below based on the value of a different column.

I found formulae in the form of =IFERROR(SUM(A10;B13;C5;D6;D8)/((A10<>0)+(B13<>0)+(C5<>0)+(D6<>0)+(D8<>0));0), =AVERAGEIF(B1:B1:B3:B3:B5:B5:B7:B7,">0") on this site & =AVERAGEIF($A$1:$A$500,D1,$B$1:$B$500) elsewhere but the problem is it bases the averages on entire ranges rather than just the five I need. I also found SUMPRODUCT didn't do much with this as it seemed to have a similar end result.

Any help would be very appreciated as I am at my wits end trying to make it work/have spent time on automating an entire workbook that hinges on this working and don't won't to tell my manager that my time was wasted.

Thank you in advance and apologies for the length.

Regards,

Simon

Answer 1

You can put the following array formula (crtl+shift+enter) in G3 (assuming based on the screenshot) and then copy down:
{=AVERAGE(IF(ROW(F3:$F$90)<=SMALL(IF(F3:$F$90<>"";ROW(F3:$F$90));MIN(COUNT(F3:$F$90);5));IF(F3:$F$90<>"";F3:$F$90)))}

Some comments :

• The 5 is hard coded in the formula. Better would be to have this in a cell somewhere, so you also can calculate the average of other days;
• I assumed the data was in F3:$F$90;
• It will also calculate the average on the blank rows, so you have to add an additional if if you prefer that the average is also blank;
• The formula also works for the last values, it will just take all values available if there are less than 5.