software

I have a lengthy spreadsheet with several columns of numerical data. Some
cells have positive numbers and others have negative numbers. My goal is to
get both a column sum and average--but only of only those cells with a
positive number--i would like to exclude the negative numbers from my
calculations.

Are there formulas that will so this for me?
--
Tia, Education and Documentation Specialist

Tia,

Sum:
=SUMIF(D,quot;gt;0quot;)

Average:
=SUMIF(D,quot;gt;0quot;)/COUNTIF(D,quot;gt;0quot;)

HTH,
Bernie
MS Excel MVPquot;Tiaquot; gt; wrote in message
...
gt;I have a lengthy spreadsheet with several columns of numerical data. Some
gt; cells have positive numbers and others have negative numbers. My goal is to
gt; get both a column sum and average--but only of only those cells with a
gt; positive number--i would like to exclude the negative numbers from my
gt; calculations.
gt;
gt; Are there formulas that will so this for me?
gt; --
gt; Tia, Education and Documentation Specialist
Try something like this:

With values in cells A1:A10

Greater than zero:
SUM
B1: =SUMIF(A1:A10,quot;gt;0quot;)
AVERAGE
B2: =SUMIF(A1:A10,quot;gt;0quot;)/COUNTIF(A1:A10,quot;gt;0quot;)

Greater than or equal to zero:
SUM
B1: =SUMIF(A1:A10,quot;gt;=0quot;)
AVERAGE
B2: =SUMIF(A1:A10,quot;gt;=0quot;)/COUNTIF(A1:A10,quot;gt;=0quot;)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Tiaquot; wrote:

gt; I have a lengthy spreadsheet with several columns of numerical data. Some
gt; cells have positive numbers and others have negative numbers. My goal is to
gt; get both a column sum and average--but only of only those cells with a
gt; positive number--i would like to exclude the negative numbers from my
gt; calculations.
gt;
gt; Are there formulas that will so this for me?
gt; --
gt; Tia, Education and Documentation Specialist

Another option for Average using an Array formula:
=AVERAGE(IF(Rnggt;0,Rng))

Rng being a range name of say A1:A10
--
HTH. :gt;)
Dana DeLouis
Windows XP, Office 2003quot;Tiaquot; gt; wrote in message
...
gt;I have a lengthy spreadsheet with several columns of numerical data. Some
gt; cells have positive numbers and others have negative numbers. My goal is
gt; to
gt; get both a column sum and average--but only of only those cells with a
gt; positive number--i would like to exclude the negative numbers from my
gt; calculations.
gt;
gt; Are there formulas that will so this for me?
gt; --
gt; Tia, Education and Documentation Specialist