Forum Discussion

Roman505's avatar
Roman505
Copper Contributor
Dec 09, 2021
Solved

Dynamic Averageif

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!

excel
Formulas and Functions
  • Juliano-Petrukio's avatar
    Juliano-Petrukio
    Dec 10, 2021

    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)))

     

2 Replies

  • Juliano-Petrukio's avatar
    Juliano-Petrukio
    Bronze Contributor
    Dec 10, 2021

    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)))