If then formula

[Libby]

I want to write a formula to do the following:

If column w= 1 and column t-u <>0 and column t-u <= 50 and column t-u >+
-100 then return the word "immaterial" in column v else if column w=1 and
column t-u = 0 then return "pb" in column v else leave column v blank

 

[bj]

=if(W1=1,if(T1-U1=0,"pb",if(and(T1-U1>-100,T1-U1<=50),"immaterial","")),"")
I assume what you meant by T-U>+-100 was just T-U>-100

 

[Libby]

Thanks, it works just like I want.
--
Libby

=if(W1=1,if(T1-U1=0,"pb",if(and(T1-U1>-100,T1-U1<=50),"immaterial","")),"")
I assume what you meant by T-U>+-100 was just T-U>-100

 

[Libby]

I lied. The formula isn't working right when w<>1. It's returning " instead
of pb,immaterial or blank.

 

[bj]

it's returning a single double quote?

are you sure your equation does not end in ...),"""") this would return a
single quote mark, but I am not sure what else would make it do that.

 

[bj]

a quote double hyphon quote would also give a ''

I lied. The formula isn't working right when w<>1. It's returning " instead
of pb,immaterial or blank.

 

[Libby]

=if(W8=1,if(T8-U8=0,"pb",if(and(T8-U8>=-100,T8-U8<=50,"immaterial","")),V8)
this is the formula that I have and I am getting an error.

Any suggestions.

 

[bj]

in this formula in W8<>1 it should give what is in V8
What is in V8 and what are you getting?

 

[Libby]

I am in column x and immaterial is in column v. I am getting an excel error
"the formula you typed contains an error.--
Libby

 

[Sandy Mann]

=if(W8=1,if(T8-U8=0,"pb",if(and(T8-U8>=-100,T8-U8<=50,"immaterial","")),V8)
You need a closing parenthesis after <=50 for the AND()

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Libby]

I think I got it. I was missing a )........thanks for your help.

Can you help with my control q question.

 

[bj]

what is the control Q question?

I think I got it. I was missing a )........thanks for your help.

Can you help with my control q question.