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))
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 ...
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
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))
The Max function is probably what you are after
=Max(0, A1+B1)
HTH
Peter
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...
As long as the formula can be copied to other rows I don't see the problem...
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
Use MAX as suggested to you:Originally Posted by vanilladan
=MAX(0,ROUND((I2/H2)*100,0)+MOD(A2/((b2*$A$5)-100))
Bat17,
Max was the perfect answer, thank you.
The simple solutions are always the best!
good work,
Vanilladan
Like this thread? Share it with others