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

1. MightyBenihanaDo or do not, there is no try

Joined:
8 Sep 2011
Posts:
1,211
24
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. XlogActive Member

Joined:
16 Dec 2006
Posts:
475
25
=IF(A1>0, A1-1, 0)

3. ZoonHunting Wabbits since the 80s

Joined:
12 Mar 2001
Posts:
4,426
337
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.

Joined:
8 Sep 2011
Posts:
1,211
24
Thanks guys

5. MightyBenihanaDo or do not, there is no try

Joined:
8 Sep 2011
Posts:
1,211
24
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. bawjawsWell-Known Member

Joined:
5 Dec 2010
Posts:
2,973
158
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. MightyBenihanaDo or do not, there is no try

Joined:
8 Sep 2011
Posts:
1,211
24
Could you explain how to do that, please?

Joined:
8 Sep 2011
Posts:
1,211