Excel Condition missplacing

Question

I am trying to make an average of multiple cells, and I want to ignore to zero cells.

Here is my formula =IFERROR(AVERAGEIF(L4:L10;L12:L18;L20:L26;L28:L34;L36:L37);"") and I don't know where to put the condition to ignore zero "<>0" . Am I doing something wrong?

Answer 1

Assuming you only have positive values and zeroes you can average without zeroes, for non-contiguous ranges using this syntax

=IFERROR(SUM(L4:L10;L12:L18;L20:L26;L28:L34;L36:L37)/INDEX(FREQUENCY((L4:L10;L12:L18;L20:L26;L28:L34;L36:L37);0);2);"")

The FREQUENCY part gives you a two element array, one being the count of zeroes, the other the count of positive values, INDEX then retrieves the second of those (the number of positive values), so if you divide the sum by that count you get the average excluding zeroes. FREQUENCY function (unlike AVERAGEIF) accepts a non contiguous range argument (a "union")

....but if you can identify which rows to exclude by using values in another column then it's easier with AVERAGEIFS, e.g. if on the excluded rows, e.g. in K11, K21, K35 etc. they all have the value "Total" you can use this version:

=IFERROR(AVERAGEIFS(L4:L37;L4:L37;"<>0";K4:K37;"<>Total");"")

adjust depending on the exact text, wildcards are possible