Two different formulas same basic problem....

S

Shhhh

Hello all these are 2 formulas that both work however I can't seem to
be able to tell it to ignore blank cells... I appreciate all your
amazing help on these forums...

Here is formula #1...
=IF(C29="",IF(G29-F29<0,"L",""),IF(F29-G29<=1,"L",""))

it works fine... however there are instances where F29 is blank and it
puts an "L" in. I want it to stay blank if F29 is blank.


Here is formula #2
=IF(F55>0,$J$11/(F55/100),IF(AND(F55>=-120,F55<=-101),I55/-(F55/100),IF
(AND(F55>=-139,F55<=-121),I55/-(F55/100),IF(F55<=-140,IF(G55>0,I55*
(G55/100),I55/-(G55/100))))))

as with above it works fine in general... however when cells F,G,I are
blank it is returning "FALSE" how can I tell it to stay blank in that
case?

Thank you all again very much.
 
R

Ragdyer

#1]
=IF(F29="","",IF(C29="",IF(G29-F29<0,"L",""),IF(F29-G29<=1,"L","")))

#2]
=IF(F55>0,$J$11/(F55/100),IF(AND(F55>=-120,F55<=-101),I55/-(F55/100),IF(AND(
F55>=-139,F55<=-121),I55/-(F55/100),IF(F55<=-140,IF(G55>0,I55*(G55/100),I55/
-(G55/100)),""))))
 
S

Shane Devenshire

Hi,

Here is a slightly different approach for the first formula

=IF(F29,IF(C29,IF(F29-G29<=1,"L",""),IF(G29-F29<0,"L","")),"")
 
S

Shane Devenshire

Hi,

In your second formula it is not clear whether you want it to be blank when
one or all of the cells are blank?

Assuming it is any of them are blank:

=IF(OR(F55="",G55="",I55=""),"",IF(F55>0,100*$J$11/F55,IF(AND(F55>=-120,F55<=-101),-100*I55/F55,IF(AND(F55>=-139,F55<=-121),-100*I55/F55,IF(F55<=-140,IF(G55>0,I55*G55/100,-100*I55/G55))))))
 
Top