kewiopex
01-27-2017, 07:22 AM
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.
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.