Need average of non-blank cells over zero.

djl0525

Well-known Member
Joined
Dec 11, 2004
Messages
1,235
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

jss1234

Active Member
Joined
May 28, 2004
Messages
434
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,403
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,119,264
Messages
5,577,081
Members
412,763
Latest member
sienweiw
Top