Dynamic Averageif

[Roman505]

I'm working with a long Table that I'm trying to make Dynamic. The Equation that I have been using up to this point is 

=IFERROR(AVERAGEIF(H3:H794,1,J3:J794),0)

H = % Earned    J=Loss Ratio

I'm trying to only Average the Loss Ratios that are also 100% Earned. This has worked fine up till now. I'm looking for a way to make this Dynamic, so if I filter on a Term or Year, it's only going to look at the Visible portions of each Column for the average. 

Let me know if you have any questions. 

Thank you!

[#2 · Accepted solution]

@Roman505 

It is a very simplified way to achieve it using array formula.

As I don't know the version of your excel, I would recommend you CTRL+SHIFT+ENTER when using the formula.

=AVERAGE(IF(SUBTOTAL(2,
         OFFSET(D2,ROW(D3:D400)-ROW(D2),0)),IF(F3:F400=1,H3:H400)))

[#3]

@Juliano-Petrukio Thank you, That's what I was looking for!