Forum Discussion

ranga87's avatar
ranga87
Copper Contributor
May 04, 2021
Solved

Remove zeros from AVERAGEIF but causes too many arguments

Hi everyone.

 

I am running a successful function:

=AVERAGEIF(D3:D10000,"*"&J64&"*",F3:F10000)

but I want to remove all 0 figures and they make the average lower than it should be as the 0 is someone not working that day.

I tried =AVERAGEIF(D3:D10000,"*"&J64&"*",F3:F10000,"<>0") and it says too many arguments. I cannot think of a way of making it work.

 

TIA

  • ranga87

    If you need multiple conditions, use AVERAGEIFS instead of AVERAGEIF. The order of the arguments is slightly different:

     

    =AVERAGEIFS(F3:F10000, D3:D10000, "*"&J64&"*", F3:F10000, "<>0")

3 Replies

  • ranga87

    If you need multiple conditions, use AVERAGEIFS instead of AVERAGEIF. The order of the arguments is slightly different:

     

    =AVERAGEIFS(F3:F10000, D3:D10000, "*"&J64&"*", F3:F10000, "<>0")

    • ranga87's avatar
      ranga87
      Copper Contributor
      Absolute king. Thanks so much. The hair pulling has finished.

      I see the added range at the front reflects what we are searching for then the parameters give us the correct calculation?
      • ranga87 

        Yes, the syntax of AVERAGEIFS is

         

        =AVERAGEIFS(range_to_average, condition_range1, condition1, condition_range2. condition2, ...)

Resources