Dynamic Averageif

New Contributor

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 




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 Replies
best response confirmed by Hans Vogelaar (MVP)


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.



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