# Formula to ignore negative numbers in a column?

1. ## Formula to ignore negative numbers in a column?

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  Register To Reply

2. ## Re: Formula to ignore negative numbers in a column?

Tia,

Sum:
=SUMIF(D:D,">0")

Average:
=SUMIF(D:D,">0")/COUNTIF(D:D,">0")

HTH,
Bernie
MS Excel MVP

"Tia" <Tia@discussions.microsoft.com> wrote in message
news:F80E5FBD-91E2-459F-A178-89487B61D1D6@microsoft.com...
>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  Register To Reply

3. ## RE: Formula to ignore negative numbers in a column?

Try something like this:

With values in cells A1:A10

Greater than zero:
SUM
B1: =SUMIF(A1:A10,">0")
AVERAGE
B2: =SUMIF(A1:A10,">0")/COUNTIF(A1:A10,">0")

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

Does that help?

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

XL2002, WinXP-Pro

"Tia" wrote:

> 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  Register To Reply

4. ## Re: Formula to ignore negative numbers in a column?

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

Rng being a range name of say A1:A10
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003

"Tia" <Tia@discussions.microsoft.com> wrote in message
news:F80E5FBD-91E2-459F-A178-89487B61D1D6@microsoft.com...
>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  Register To Reply

5. ## Re: Formula to ignore negative numbers in a column?

Hello,

Is there a way i could add a if negative do not count to this?

=IF(ROUNDDOWN(((J29+(MIN(J30:J36)))*0.6)-J29,0)<0,0,ROUNDDOWN(((J29+(MIN(J30:J36)))*0.6)-J29,0))

Much thanks!  Register To Reply

6. ## Re: Formula to ignore negative numbers in a column?

Welcome to the forum. We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread  Register To Reply