Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 |
#2
![]() |
|||
|
|||
![]()
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 |
#3
![]() |
|||
|
|||
![]() =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 |
#4
![]() |
|||
|
|||
![]()
"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),"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|