1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel help needed please

Discussion in 'Software' started by MightyBenihana, 28 Nov 2017.

  1. MightyBenihana

    MightyBenihana Do or do not, there is no try

    Joined:
    8 Sep 2011
    Posts:
    1,221
    Likes Received:
    29
    I am having a problem whereby I want a cell to -1 from another cell unless that cell = 0. If 0 then I want it to remain at 0 (i.e. not go into negative numbers)

    For example:

    If A1= greater than 0 then -1, if A1= 0 then return 0.

    I hope that makes sense and someone can help.

    Thanks
     
  2. Xlog

    Xlog Active Member

    Joined:
    16 Dec 2006
    Posts:
    487
    Likes Received:
    27
    =IF(A1>0, A1-1, 0)
     
  3. Zoon

    Zoon Hunting Wabbits since the 80s

    Joined:
    12 Mar 2001
    Posts:
    4,521
    Likes Received:
    363
    I’d use

    =IF($A1>0, $A1-1, $A1)

    Depends whether you want to force 0 or copy the value I.e. this will allow a negative value.
     
  4. MightyBenihana

    MightyBenihana Do or do not, there is no try

    Joined:
    8 Sep 2011
    Posts:
    1,221
    Likes Received:
    29
    Thanks guys
     
  5. MightyBenihana

    MightyBenihana Do or do not, there is no try

    Joined:
    8 Sep 2011
    Posts:
    1,221
    Likes Received:
    29
    OK I am closer to a solution but I think my explanation wasn't great.

    The answers above leave me with zero because there is nothing in the Feb '18 box to minus 1 from. The cell is blank, I need the formulae to provide the number.

    I will try to explain better:

    I have sheet Jan'18 which shows installment payments remaining, so in Jan there are 6 payments remaining, in Feb '18 there will be 5 and so on until zero payments remaining. What I am trying to do is get a formula that automatically reduced the number of installments across the sheets until zero and when it reaches zero it remains at zero and doesn't go to -1.
     
  6. bawjaws

    bawjaws Well-Known Member

    Joined:
    5 Dec 2010
    Posts:
    3,059
    Likes Received:
    194
    The easiest way of doing this is probably to use the MAX function - i.e. = MAX (A1-1,0) will reduce A1 by one but the lowest it'll go is 0.
     
  7. MightyBenihana

    MightyBenihana Do or do not, there is no try

    Joined:
    8 Sep 2011
    Posts:
    1,221
    Likes Received:
    29
    Could you explain how to do that, please?
     
  8. MightyBenihana

    MightyBenihana Do or do not, there is no try

    Joined:
    8 Sep 2011
    Posts:
    1,221
    Likes Received:
    29
    Think i got it
     

Share This Page