using OR with IF function?

  • Thread starter Thread starter Ron
  • Start date Start date
R

Ron

Hello,

I have a cell (cell1) with this formula

=IF((K11)="","",SUM((K11)/(AB12))*1000)

Then I have another cell (cell2) which derives its value
from cell1 (and another cell)

=IF((K14)>0, IF((K11)<>"",SUM(K14+K11),""),"")

If the value in cell1 = 0 then I want to modify the
formula for cell2 (pseudocode here) to look something like
this:

=IF((K14)>0, IF((K11)<>"" OR ((K11) > 0),SUM
(K14+K11),""),"")

Obviously, this formula doesn't work. Is there a way to
write a formula with an OR clause along with the IF
statement?

Thanks,
Ron
 
Hi Ron

(bit confusing with all of the brackets and extraneous "SUMs")

- formula 1 in cell 1
=IF(K11="","",(K11/AB12)*1000)

- formula 2 in cell 2
=IF(K14>0,IF(K11<>"",K14+K11,""),"")

now you want
=IF(K14>0,IF(OR(K11<>"",K11>0),K14+K11,""),"")

Hope this helps
Cheers
JulieD
 
Hi
first you could simplify your existing formulas a little bit :-)
=IF((K11)="","",SUM((K11)/(AB12))*1000)
becomes
=IF(K11="","",K11/(AB12*1000))

and
=IF((K14)>0, IF((K11)<>"",SUM(K14+K11),""),"")
becomes
=IF(K14>0,IF(K11<>"",K14+K11,""),"")

for your formula request try:
=IF(K14>0,IF(OR(K11<>"",K11>0),K14+K11,""),"")
 
First, in your original formulae, SUM() is superfluous:

=IF(K11="","",K11/AB12*1000)
=IF(K14>0,IF(K11<>"",K14+K11,""),"")

are equivalent.

To use OR:

=IF(K14>0,IF(OR(K11<>"",K11>0),K14+K11,""),"")

However, that's relatively meaningless since if K11>0, it is by
definition not a null string. Since a null string will be interpreted as
0 in a comparison involving numbers, you can simply use

=IF(K14>0,IF(K11>0,K14+K11,""),"")

or, more efficiently

=IF(AND(K14>0,K11>0),K14+K11,"")
 
Note that the porposed substitute is a million times too small <g>.
Perhaps

=IF(K11="","",K11/AB12*1000)
 
Thanks all for your help. I was actually wondering about
the superfluous Sum, I think I inherited these formulas
(not to pass the buck), but now I will fix these as you
folks have suggested. Thanks also for the demos on using
Or, and And, I was also going to ask about And.

Ron
 
Back
Top