Eliminate negative numbers from stats

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
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?


Could this be done with a pivot table? I know a pivot table would do a lot
of what I'd like done.

Thanks for your help,
Ellen
 
Ellen,

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.

--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
Thanks, Earl. Your formula was quite helpful.

Earl Kiosterud said:
Ellen,

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.

--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
Click to expand...
 
Sat, 2 Jun 2007 20:58:39 -0400 from Earl Kiosterud
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.
Click to expand...

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.
 
Back
Top