Average with zeroes obstacle

=AVERAGE(IF(A1:A22=0,"",A1:A22)) will average non-zero values in cells A1 to A22. You need to enter it as an array formula (after you enter the formula, press ctrl+shift+enter instead of just enter).

Perhaps something like this:

=AVERAGE(IF(N22:N28<>0,N22:N28))

Edit;
Use ctrl. shift. enter to confirm

Easier yet is the SUBTOTAL function, with 1 as the first argument, which will ignore the zeros.

=SUBTOTAL(1,A1:A100)

will return the average of all the non-zeros in A1 thru A100.

Hi Barry,
I tried the subtotal function and it seems to ignor blanks but includes zeros; unless the zeros are filtered out.

Barry Katcher said:

Easier yet is the SUBTOTAL function, with 1 as the first argument, which will ignore the zeros.

=SUBTOTAL(1,A1:A100)

will return the average of all the non-zeros in A1 thru A100.

Click to expand...

Or using 2rrs layout, the following non-array formulation

=SUM(N22:N28)/COUNTIF(N22:N28,"<>0")

This excludes zeros, blanks and text from the divisor

Here's my 2 cents worth,

=SUM(A1:A4)/MAX(1,COUNTIF(A1:A4,">0"))

or

=SUM(A1:A4)/MAX(1,COUNT(A1:A4)-COUNTIF(A1:A4,0))

An overview of formulas...

[1] in C2, requires control+shift+enter...

=AVERAGE(IF(A1:A10=0,"",A1:A10))

Note. The IF bit computes: {"";4;2;"";"";2.7;"x";3.4;-1.8;""}.

[2] in C3, requires control+shift+enter...

=AVERAGE(IF(A1:A10,A1:A10))

Note. The IF bit computes: {FALSE;4;2;FALSE;#VALUE!;2.7;#VALUE!;3.4;-1.8;FALSE}.

[3] in C4, requires control+shift+enter...

=AVERAGE(IF(A1:A10<>0,A1:A10))

Note. The IF bit computes: {FALSE;4;2;FALSE;"";2.7;"x";3.4;-1.8;FALSE}.

[4] in C5

=SUM(A1:A10)/COUNTIF(A1:A10,"<>0")

[5] in C6

=SUM(A1:A10)/(COUNTIF(A1:A10,">0")+COUNTIF(A1:A10,"<0"))

[6] in C7

=SUM(A1:A10)/MAX(1,COUNTIF(A1:A10,">0"))

[7] in C8

=SUM(A1:A10)/MAX(1,COUNT(A1:A10)-COUNTIF(A1:A10,0))

See the exhibit for preconditions regarding each of the foregoing formulas.

In addition to other fine contributions, with blanks and text entries in the midst, you may also want to try the following non-array formulation ...

=SUM(A1:A10)/SUMPRODUCT(--(A1:A10<>0)*(ISNUMBER(A1:A10)))