IF statement (positive = positive ; negative = zero)

[vanilladan]

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

 

[jimboy]

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

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

 

[bat17]

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

HTH

Peter

 

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

 

[jimboy]

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

 

[bosco_yip]

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

 

[Aladin Akyurek]

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))

 

[vanilladan]

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

good work,

Vanilladan