A Rolling AverageIfs Formula Needed For a Number of Indicators

kewiopex

New Member
Joined
Oct 30, 2016
Messages
9
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
Joined
Oct 30, 2016
Messages
9
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.
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top