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?

Hi,

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

Cheers!
Jean-Guy

"latripl" wrote:

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?

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"latripl" wrote:

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?

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

"latripl" wrote:

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?

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


"pinmaster" wrote:

Hi,

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

Cheers!
Jean-Guy

"latripl" wrote:

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?

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

"Tammy" wrote:

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


"pinmaster" wrote:

Hi,

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

Cheers!
Jean-Guy

"latripl" wrote:

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?