Clever Geek Handbook

Excel Terms of Service

I am trying to do on average a few cells and I want to ignore null 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?

+3


source to share


2 answers


Assuming you only have positive values ​​and zeros, you can average without zeros, 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 part FREQUENCY

gives you an array of two elements, one of which is the number of zeros and the other is positive values, INDEX

then extracts the second one (the number of positive values), so if you divide the sum by that count, you get the mean excluding the zeros. The function FREQUENCY

(as opposed to AVERAGEIF

) accepts a non-overlapping range argument ("union")



.... but if you can figure out which rows to exclude using values ​​in another column, then it's easier with AVERAGEIFS

eg. if on excluded lines, eg. 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");"")

configurable based on exact text, wildcards possible

+2


source


Here's another way to use SUMIF

and COUNTIF

.

Sample data:

Values
1
-3
0
5
777
3
0
0
8
text
4
5
6
0
6
7


Formulas

B18=SUMIF(A2:A17,"<>0",A2:A17)-SUM(A6,A11,A15)
B19=COUNTIF(A2:A5,"<>0")+COUNTIF(A7:A10,"<>0")+COUNTIF(A12:A17,"<>0")
B20=B18/B19

enter image description here

0


source






More articles:


All Articles