Results 1 to 8 of 8

IF statement (positive = positive ; negative = zero)

This is a discussion on IF statement (positive = positive ; negative = zero) within the Excel Questions forums, part of the Question Forums category; Hello folks, I'm writing a lot of IF statements at the moments which involve themes such as: =IF(A1+B1 only much ...

  1. #1
    Board Regular
    Join Date
    Sep 2002
    Location
    Leeds
    Posts
    145

    Default IF statement (positive = positive ; negative = zero)

    Hello folks,
    I'm writing a lot of IF statements at the moments which involve
    themes such as:

    =IF(A1+B1<0,0,A1+B1)

    only much longer.
    The idea of these formulas is, as above, to say:

    "If the result is positive, return that positive result.
    If the result is negative, return a 0."

    (It's a purchasing system, so you can't buy in negative stock, i.e. -12 apples).

    What I want to know is, if there an easier way to do this
    because each formula uses a duplicate statement, which is a pain in large formulas.

    Any ideas?
    cheers,

    Vanilladan

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    2,317

    Default Re: IF statement (positive = positive ; negative = zero)

    This will do the same thing, not sure if this is what you are after (can you post some more detail)

    Code:
    =IF(SUM(A1:B1)<0,0,SUM(A1:B1))

  3. #3
    Board Regular
    Join Date
    Aug 2003
    Location
    Maidstone, Kent UK
    Posts
    1,470

    Default

    The Max function is probably what you are after
    =Max(0, A1+B1)

    HTH

    Peter

  4. #4
    Board Regular
    Join Date
    Sep 2002
    Location
    Leeds
    Posts
    145

    Default Re: IF statement (positive = positive ; negative = zero)

    Jim,
    Thanks for your reply.
    The problem I've got - which remains in your formula - is that we're duplicating statements. Let's replace sum(A1+B1) [or whatever] with
    "X". The statement says.

    If (X>0,0,X)

    We are using X twice: once as the criteria and once as the result.
    Now imagine X is a statement like:

    =ROUND((I2/H2)*100,0)+MOD(A2/(sum(b2*$A$5)-100))

    And you can appreciate the problems I have...

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Posts
    2,317

    Default Re: IF statement (positive = positive ; negative = zero)

    As long as the formula can be copied to other rows I don't see the problem...

  6. #6
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,179

    Default Re: IF statement (positive = positive ; negative = zero)

    Try the following code to change all negative to zero :

    Sub negativezeror()
    For Each Cell In Range("A1:A20")
    If Cell.Value <= 0 Then
    Cell.Value = 0
    Else
    Cell.Value = Cell.Value
    End If
    Next
    End Sub


    Regards

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,759

    Default Re: IF statement (positive = positive ; negative = zero)

    Quote Originally Posted by vanilladan
    Jim,
    Thanks for your reply.
    The problem I've got - which remains in your formula - is that we're duplicating statements. Let's replace sum(A1+B1) [or whatever] with
    "X". The statement says.

    If (X>0,0,X)

    We are using X twice: once as the criteria and once as the result.
    Now imagine X is a statement like:

    =ROUND((I2/H2)*100,0)+MOD(A2/(sum(b2*$A$5)-100))

    And you can appreciate the problems I have...
    Use MAX as suggested to you:

    =MAX(0,ROUND((I2/H2)*100,0)+MOD(A2/((b2*$A$5)-100))

  8. #8
    Board Regular
    Join Date
    Sep 2002
    Location
    Leeds
    Posts
    145

    Default Re: IF statement (positive = positive ; negative = zero)

    Bat17,
    Max was the perfect answer, thank you.
    The simple solutions are always the best!

    good work,

    Vanilladan

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com