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,201
    Likes Received:
    23
    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 Member

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

    Zoon Hunting Wabbits since the 80s

    Joined:
    12 Mar 2001
    Posts:
    4,070
    Likes Received:
    256
    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,201
    Likes Received:
    23
    Thanks guys
     
  5. MightyBenihana

    MightyBenihana Do or do not, there is no try

    Joined:
    8 Sep 2011
    Posts:
    1,201
    Likes Received:
    23
    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:
    2,750
    Likes Received:
    108
    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,201
    Likes Received:
    23
    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,201
    Likes Received:
    23
    Think i got it
     

Share This Page