average a continuous group of numbers and negative numbers are 0

[Dumbfounded]

I'm trying to get an average of a group of numbers. There are some negative
numbers and I want them to be considered 0.

 

[John C]

=SUMIF(A1:A6,">0")/COUNT(A1:A6)
Will only add up numbers that are greater than 0, then divide by the total
number of numbers

 

[Bernd P]

Just for the fun of it:
array-enter
=AVERAGE((A1:A6>0)*A1:A6)

Regards,
Bernd

 

[Chip Pearson]

If you want the negatives to count as 0 (as opposed to being excluded
from the average altogether) use

=AVERAGE(IF(A1:A5=0,0,A1:A5))

If you want to exclude negatives from the average, use

=AVERAGE(IF(A1:A5<0,FALSE,A1:A5))

Note that averaging a zero is very different that excluding the value
from the average.

These are both array formulas, so you must press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula and whenever
you edit it later. If you do this properly, Excel wil display the
formula enclosed in curly braces. See
www.cpearson.com/Excel/ArrayFormuals.aspx for more info about array
formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)