+ Reply to Thread
Results 1 to 6 of 6

Changing negative numbers to zero

  1. #1
    Chet T
    Guest

    Changing negative numbers to zero

    Hi all,

    Is there any way to automatically change any negative numbers in a
    cell to zero? For a spend down spreadsheet that I am developing, any
    negative numbers will be zero. I could not find a format or function
    that would do it. I would like to avoid the =if(.......>0,......,0)
    route because the formula in the cell already contains an if argument
    and it is already too complicated. Would an array formula work? I
    haven't worked with them too much. Any advice is appreciated.

    TIA

    Chet

  2. #2
    B. R.Ramachandran
    Guest

    RE: Changing negative numbers to zero

    Hi,

    If those numbers (negative as well as positive) are the outcome of a
    formula, you may modify the formula as,
    =if(formula<0,0,formula)

    Regards,
    B. R. Ramachandran


    "Chet T (Chet T)" wrote:

    > Hi all,
    >
    > Is there any way to automatically change any negative numbers in a
    > cell to zero? For a spend down spreadsheet that I am developing, any
    > negative numbers will be zero. I could not find a format or function
    > that would do it. I would like to avoid the =if(.......>0,......,0)
    > route because the formula in the cell already contains an if argument
    > and it is already too complicated. Would an array formula work? I
    > haven't worked with them too much. Any advice is appreciated.
    >
    > TIA
    >
    > Chet
    >


  3. #3
    B. R.Ramachandran
    Guest

    RE: Changing negative numbers to zero

    Hi,

    I apologize for my earlier reply to your posting. I didn't notice that do
    not want to use the IF function to change negative numbers to zero. Sorry.

    Regards,
    B. R. Ramachanrdan

    "Chet T (Chet T)" wrote:

    > Hi all,
    >
    > Is there any way to automatically change any negative numbers in a
    > cell to zero? For a spend down spreadsheet that I am developing, any
    > negative numbers will be zero. I could not find a format or function
    > that would do it. I would like to avoid the =if(.......>0,......,0)
    > route because the formula in the cell already contains an if argument
    > and it is already too complicated. Would an array formula work? I
    > haven't worked with them too much. Any advice is appreciated.
    >
    > TIA
    >
    > Chet
    >


  4. #4
    Dave Peterson
    Guest

    Re: Changing negative numbers to zero

    How about just:

    =max(0,youroriginalformulahere)

    If your formula returns -3, then 0 is larger and will be returned.

    If your formula returns +33, then that's bigger than 0. So you'll see 33.

    Chet T wrote:
    >
    > Hi all,
    >
    > Is there any way to automatically change any negative numbers in a
    > cell to zero? For a spend down spreadsheet that I am developing, any
    > negative numbers will be zero. I could not find a format or function
    > that would do it. I would like to avoid the =if(.......>0,......,0)
    > route because the formula in the cell already contains an if argument
    > and it is already too complicated. Would an array formula work? I
    > haven't worked with them too much. Any advice is appreciated.
    >
    > TIA
    >
    > Chet


    --

    Dave Peterson

  5. #5
    Chet T
    Guest

    Re: Changing negative numbers to zero

    Thanks Dave!

    That did it.

    Chet

    On Thu, 06 Oct 2005 19:01:54 -0500, Dave Peterson
    <petersod@verizonXSPAM.net> wrote:

    >How about just:
    >
    >=max(0,youroriginalformulahere)
    >
    >If your formula returns -3, then 0 is larger and will be returned.
    >
    >If your formula returns +33, then that's bigger than 0. So you'll see 33.
    >
    >Chet T wrote:
    >>
    >> Hi all,
    >>
    >> Is there any way to automatically change any negative numbers in a
    >> cell to zero? For a spend down spreadsheet that I am developing, any
    >> negative numbers will be zero. I could not find a format or function
    >> that would do it. I would like to avoid the =if(.......>0,......,0)
    >> route because the formula in the cell already contains an if argument
    >> and it is already too complicated. Would an array formula work? I
    >> haven't worked with them too much. Any advice is appreciated.
    >>
    >> TIA
    >>
    >> Chet

    >
    >--
    >
    >Dave Peterson



  6. #6
    Chet T
    Guest

    Re: Changing negative numbers to zero

    Thanks for the response B.R.

    The formula already hase one if statement in it and I'm not sure that
    I am good enough at excel yet to try to deal with two if statements.

    Dave's =MAX(0,formula) response did the trick.

    Thanks again.

    Chet

    On Thu, 6 Oct 2005 15:25:02 -0700, "B. R.Ramachandran"
    <BRRamachandran@discussions.microsoft.com> wrote:

    >Hi,
    >
    >I apologize for my earlier reply to your posting. I didn't notice that do
    >not want to use the IF function to change negative numbers to zero. Sorry.
    >
    >Regards,
    >B. R. Ramachanrdan
    >
    >"Chet T (Chet T)" wrote:
    >
    >> Hi all,
    >>
    >> Is there any way to automatically change any negative numbers in a
    >> cell to zero? For a spend down spreadsheet that I am developing, any
    >> negative numbers will be zero. I could not find a format or function
    >> that would do it. I would like to avoid the =if(.......>0,......,0)
    >> route because the formula in the cell already contains an if argument
    >> and it is already too complicated. Would an array formula work? I
    >> haven't worked with them too much. Any advice is appreciated.
    >>
    >> TIA
    >>
    >> Chet
    >>



+ Reply to 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