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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

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