# A Rolling AverageIfs Formula Needed For a Number of Indicators

#### kewiopex

##### New Member
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.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### kewiopex

##### New Member
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.

Replies
2
Views
251
Replies
7
Views
432
Replies
3
Views
588
Replies
1
Views
848
Replies
1
Views
2K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,784
Messages
5,833,682
Members
430,222
Latest member
Nickkarl

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back