Closed Thread
Results 1 to 6 of 6

Formula to ignore negative numbers in a column?

  1. #1
    Tia
    Guest

    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

  2. #2
    Bernie Deitrick
    Guest

    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




  3. #3
    Ron Coderre
    Guest

    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


  4. #4
    Dana DeLouis
    Guest

    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




  5. #5
    Registered User
    Join Date
    01-29-2016
    Location
    Toronto,Canada
    MS-Off Ver
    Office 2007
    Posts
    6

    Re: Formula to ignore negative numbers in a column?

    Hello,

    Not to bring up a dead thread but..

    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!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: Formula to ignore negative numbers in a column?

    Administrative Note:

    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.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

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


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1