Formula for average with a "IF" statement

[Guest]

I am trying to do a formula for averaging a series of numbers (which I can
do) exept if there is zero in the series I want to ignore it in the avg. How
do I write an IF statement for this?

 

[Guest]

Hi,

=AVERAGE(IF(A1:A100<>0,A1:A100))
enter using Ctrl+Shift+Enter

Cheers!
Jean-Guy

 

[Guest]

Two ways:
1) Create an array formula: http://www.mrexcel.com/tip011.shtml
2) SUMIF(...)/COUNTIF(...) where the criteria for both functions is "greater
than zero". (The sum of the items greater than zero in your set divided by
the count of the items greater than zero in your set.)

Since you don't provide detail it's impossible to give you an accurate
formula. Post back if you have questions about either option.

Dave

 

[Guest]

How about =sum($A:$A)/(countif($A:$A,">0"))

 

[Guest]

pinmaster -
how would i modify that statement to EXCLUDE blank cells but include > 0 ?
tammy

 

[Guest]

No need.... blank cells and text does not effect an average formula, as it
stands it will average values below and above 0 but not 0.

HTH
Jean-Guy