IF formula problem

J

Joe Gieder

First, thank you for looking at his and your expert help and advice.

I have this Array entered formula in cell V1:
=IF('Priced BOM'!$W$1<>(OR((LEFT('Priced BOM'!$W$1,3)="DLA"),(LEFT('Priced
BOM'!$W$1,3)="GFM"))),"Quote",'Priced BOM'!$W$1)

and it give the wrong result, it evaluates to True when it should be False
because W1 has the value "DLA".
I need for it to say "Quote" if cell W1 does not contain the text "DLA" or
"GFM" and state the value that is in W1 if it evaluates to False. Is there a
way to use OR in the formula or can what I want to do just not be done?

Thank you in advance for your help
Joe
 
P

Peo Sjoblom

Maybe this

=IF(OR(LEFT('Priced BOM'!$W$1,3)<>"DLA",LEFT('Priced
BOM'!$W$1,3)<>"GFM"),"Quote",'Priced BOM'!$W$1)

another way

=IF(OR(ISERR(FIND(,"DLA",'Priced BOM'!$W$1)),ISERR(FIND("GFM",'Priced
BOM'!$W$1))),"Quote",'Priced BOM'!$W$1)

which is case sensitive and will work if there are leading spaces etc
replace with SEARCH if not case sensitive

Regards,

Peo Sjoblom
 
R

Richard Buttrey

First, thank you for looking at his and your expert help and advice.

I have this Array entered formula in cell V1:
=IF('Priced BOM'!$W$1<>(OR((LEFT('Priced BOM'!$W$1,3)="DLA"),(LEFT('Priced
BOM'!$W$1,3)="GFM"))),"Quote",'Priced BOM'!$W$1)

and it give the wrong result, it evaluates to True when it should be False
because W1 has the value "DLA".
I need for it to say "Quote" if cell W1 does not contain the text "DLA" or
"GFM" and state the value that is in W1 if it evaluates to False. Is there a
way to use OR in the formula or can what I want to do just not be done?

Thank you in advance for your help
Joe

=IF(AND(LEFT('Priced Bom'!$W$1,3)<>"DLA",LEFT('Priced
Bom'!$W$1,3)<>"GFM"),"Quote",'Priced Bom'!$W$1)


Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
J

Joe Gieder

Thank you for your help.
I tried the below formulas but they both seem to evaluate to True all the
time because the result ends up being "Quote". Am I possibly doing something
wrong?

Thanks
Joe
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top