Thanks, Stan. Yes I want the uncompleted project days to be blank.

"Stan Brown" wrote:

Sat, 2 Jun 2007 20:58:39 -0400 from Earl Kiosterud
:
"EllenM" wrote in message
...
I am tracking days for project completions. I do this my
subtracting the start date from the completion date. The days of
the uncompleted projects are negative numbers. Among other stats,
I'd like to do averages on completed tasks. What's the best way
to eliminate the negative numbers from the averages?

If the days (some positive, some negative) are in C2:C10), you could use:

=AVERAGE(IF(C2:C10=0,C2:C10,""))

This is an array formula, and must be committed with
Ctrl-Shift-Enter, not just Enter, any time it's been edited. It
includes zero days. Change = to to exclude zero days.

Do you *want* the days of the uncompleted projects to be negative
numbers? If you'd prefer for them to be blanks, then replace your
subtraction:
=B2-A2
with
=if(B2=A2,B2-A2,"")

Then your average will work just fine because =AVERAGE ignores non-
numeric cells. No array formulas or other special stuff needed.


--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/