A Rolling AverageIfs Formula Needed For a Number of Indicators

[kewiopex]

Dear Experts and Specialists
I am greatly in need for a formula for myself and others to easily adjust the ranges of the Averageifs formula for each new quarter of information that is added. In other words, the Averageifs needs to dynamically adjust so that it calculates a rolling average over a period of the latest 8 quarters when 8 or more quarters are available. It should also execute an average if less than 8 quarters of data is present.

The formula needs to adjust the ranges by moving the starting range position and the ending range by 1 quarter. Each quarter has 7 columns of data, CN1,CN2, CN3, CN4, CN5, CN6 and NB that repeat for each quarter. NB is not included as part o the average and hence the averageifs.

Any help to get me started would be wonderful.

 

[kewiopex]

Hello All
I have just completed a formula that will accomplish the above But it will be necessary to modify for each indicator for each row. This formula is not configured to do a drop and fill down copying and perhaps someone has the knowledge to do this. Here is the version of the formula that I used:

Here is the version of the formula that I used:
=AVERAGEIFS(OFFSET($K$4,0,COUNT($4:$4),,-MIN(56,COUNT($4:$4))),OFFSET($K$4,-1,COUNT($4:$4),,-MIN(56,COUNT($4:$4))),"<>NB").

This formula does the calc for row 4 while it looks at row 3 as the criteria row (the 2nd offset refers to the row 3 with the -1. The -MIN(56,COUNT($4:$4) makes the offset go back 8 quarters worth ( seven columns by 8 or 56 columns OR thepresent count of columns whichever is less)

Any suggestions would be most welcomed.