Need average of non-blank cells over zero.

[djl0525]

Can someone help me with a formula, please? I want to find the average of the entire column A, but I want to ignore the 0 values. Say there's 1000 values and 50 of them are 0. I want the average of the 950 non-blank cells.

Thanks -- DJ

 

[jss1234]

You can use a formula in a column to the right - ie:

=IF(A1=0,"",A1)

Then average the new column. The "" will leave the column blank, and the average will ignore it automatically.

 

[Domenic]

To average values greater than zero, try...

=AVERAGE(IF(A1:A1000>0,A1:A1000))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!