IF & AND formulae

[Pam @ Joyce]

I am trying to say if I4 has something in it and K4=X, then put 200 in this
field, but if not see if I4 has something in it and J4 >0 and L= YES then
times J4 by 1.5, if not put 0. my formulae is

=+IF(AND(I4>0,K4=x),200,IF(AND(I4>0,J4>0,L=YES),J4*1.5,0))
I get #NAME?
can someone help me get this right please?

 

[Mike H]

Hi,

There are a couple of problems with the formula

1. The x needs to be in quotes as does the "Yes"
2. L="Yes"

What is L?

Try this

=IF(AND(I4>0,K4="x"),200,IF(AND(I4>0,J4>0,L4="Yes"),J4*1.5,0))

Mike

 

[Pam @ Joyce]

Thank you Mike - this fixed half my problem - I now get the 200, however
still get #Name? for the other half. I added in a column and put the Yes in
its own column (M) as I was using L for comments as well as the YES in L, but
this didn't work either. I can have 3 logical tests in one AND statement
can't I?

Pam

 

[Pam @ Joyce]

Hi Mike - ok its official I'm an idiot, its just dawned on me by what you
meant about what is L. L should have been L4. So I've gone back and fixed
this and yes your formulae now works - thank you again so much. You don't
know what a relief it is to be able to pick an experts brain like you are
able to do in this discussion group. I've only ever posted two questions and
that was yesterday, both of which you solved for me.
Regards
Pam