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. 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,

2. 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. The Max function is probably what you are after
=Max(0, A1+B1)

HTH

Peter

4. Re: IF statement (positive = positive ; negative = zero)

Jim,
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. 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. 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. Re: IF statement (positive = positive ; negative = zero)

Jim,
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. Re: IF statement (positive = positive ; negative = zero)

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

good work,