Forum Discussion
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
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
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")
- 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?Yes, the syntax of AVERAGEIFS is
=AVERAGEIFS(range_to_average, condition_range1, condition1, condition_range2. condition2, ...)
