AverageIfs Dynamic Formula -Criteria Not Working?

[kewiopex]

Dear Experts
I have created a averageifs dynamic formula that calculates a rolling average over the last 8 quarters using the offset nested function and using counta.

When I validate the formula manually using average, I do not get agreement. Here is the formula:=AVERAGEIFS(OFFSET(AC4,0,COUNTA($AD$4:$CT$4),,-48),OFFSET($AC$4,0,COUNTA($AC$4:$CT$4),-1,-48),"<>T")

Q1. Although I am not sure, it appears that the criteria is not working and therefor do not get agreement when I do a manual check. The other complications are that the calc range includes blank cells.
Q2. How do I make the Counta range dynamic? I tried using COUNTA($4:$4) and was getting circular reference error until I specified a range outside of the cell containing the formula?

Would you please let me know where I am going wrong?

Gratefully yours.

[kewiopex]

With a little research, I have a solution that works. I read that the Averageifs will count blank cells as a 0. This is I assume the reason why the above formula would not work.

So I experimented and discovered that if I worked in the continuous criteria range (Row C) in the attached file for the both the average_range and the criteria range, it calculated the correct Rolling Average over the 8 quarters ( last 48 columns in the file ) and is now dynamic.

The formula reworked is =AVERAGEIFS(OFFSET($F$4,0,COUNTA($3:$3)-6,,-48),OFFSET($F$3,0,COUNTA($3:$3)-6,,-48),"<>T"). This reworked formula also enablesd a true dynamic range across the row (using $3:$3).

With respect to Q2:How do I make the Counta range dynamic? I tried using COUNTA($4:$4) and was getting circular reference error....by using the Row 3, I was able to avoid the row in which the formula cell appears so that the cell containing the formula is not part of the formula.

If there any other approaches then please let me know.

[SamT]

You did not use Dates in Row 1 and did use Merged cells. Both of which are anathemas to good formulas and Macros.

I don't even have any suggestions, since it appears that your attachment is but a small part of the entire workbook.

[kewiopex]

Oh no. Thank you. Unfortunately the spreadsheet is not my design and I have no latitude with the dates etc. So best to go with reality in this case.