Find AVG/MIN of a Column, excluding 0's and NULL's?
 

Hello,
How do I average a column of numbers, but exclude cells that contain 0's or
Null's? I don't want 0 figured into the average value.
Secondly how do I find the MIN, but exclude 0's in the calculation?
Thanks in advance.
JT

Sorry for the triple post. I keep getting an error message after posting to
try again later.
JT

"JT" wrote:

Hello,
How do I average a column of numbers, but exclude cells that contain 0's or
Null's? I don't want 0 figured into the average value.
Secondly how do I find the MIN, but exclude 0's in the calculation?
Thanks in advance.
JT

=IF(COUNT(A1:A10),MAX(IF(A1:A10,A1:A10)),"")

OR

=IF(COUNT(A1:A10),MIN(IF(A1:A10,A1:A10)),"")

These formulas need to be entered using CONTROL+SHIFT+ENTER.

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=275291

"JT" wrote...
How do I average a column of numbers, but exclude cells that contain 0's or
Null's? I don't want 0 figured into the average value.
Secondly how do I find the MIN, but exclude 0's in the calculation?

Excel's AVERAGE, MAX and MIN functions always ignore blank cells, which is
what I'm guessing you mean by 'null'. Since #NULL! is a possible error
value, 'null' either means that error value or is ambiguous. Blank is the
term used in Excel help, and is determined by the ISBLANK function, so use
'blank' if you mean blank cells.

As for excluding numeric zeros, unless all your data is either positive or
negative, zero would be a possible value, so shouldn't be excluded. The
following nonarray formulas process only positive values.

=IF(COUNTIF(Range,"0"),SUMIF(Range,"0")/COUNTIF(Range,"0"),"")

=IF(COUNTIF(Range,"0"),MAX(Range),"")

=IF(COUNTIF(Range,"0"),LARGE(Range,COUNTIF(Range, "0")-1),"")