Excel Help

[Adam]

Basically, I need a formula to achieve the following..
Return a result in a particular cell (L1) based on the following IF conditions..
If particular cell (F1) = Yes then return Yes
If particular cell (F1) = No then return No
and then if...
"Yes" is in the same cell (F1) and there are also 2 other matches (Cisco 887Va and/or ADSL WIC) in another cell (G2) then then return N/A in (L1).

This is what I have which works for the first part which is easy, its the AND IF parts which don't seem to work, I am not sure if I should be using AND, OR, or both?

=IF(F2="Yes","Yes",IF(F2="No","No", IF(AND(G2="Cisco 887VA",F2="Yes"),"N/A",IF(AND(G2="ADSL WIC",F2="Yes"),"N/A"))))

Cheers
Adam

 

[Tim.]

Just out for lunch. If nobody else has picked this up in the meantime I'll take a look.

 

[Adam]

Cheers

 

[mace¬]

Just out for lunch. I'l be back five minutes after @Tim. has given you the answer.

 

[The Boosh!]

The reason it isn't working is because excel is meeting all of the conditions before it can get to the other bit of your formula. i.e. a the result will only ever be yes or no. Formula done.

You're nearly there. Just a tweak in your ordering and another nested if. Does this work?

=IF(F1="No","No",IF(F1="Yes",IF(OR(G2="Cisco 887Va",G2="ADSL WIC"),NA(),"Yes")))

 

[Adam]

Yeah I did think that, and then I started to get confused between AND and OR nesting.
Just looking at the above, is that correct in terms of if F1 = Yes then I just want "Yes" in L1 but if F1 = Yes and G2 = Cisco 887Va or ADSL WIC then it will return "N/A" in L1?

 

[Adam]

Sorry - that works, apart from returning N/A, it returns #NAME?... doesn't the N/A need to be "N/A", but you can't do that to the above formula as it errors?

 

[Adam]

Sorted, was having a moment ...

=IF(F2="No","No",IF(F2="Yes",IF(OR(G2="Cisco 887Va",G2="ADSL WIC"),"N/A","Yes")))

 

[Adam]

Cheers for the help!

 

[The Boosh!]

The NA() returns an NA but I think the functionality of it depends on what version of Excel you're using, so yeh "N/A" will do the same thing

 

[Adam]

Yeah, my Excel (2010) didn't like it... all sorted now

 

[Adam]

On another point, is there a way to use an IF formula to look in a cell and match a return if the cell contains any text with "ADSL"?

 

[The Boosh!]

You use wild card matching:
=COUNTIF(A:A,"*ADSL*")

 

[Adam]

I don't want to count it, I want to return Yes, No etc

 

[Adam]

=IF(ISNUMBER(SEARCH("*ADSL*",B2)),"Yes","No")