Shorten the AND() options

[gsvirdi]

Hello,

How can I shorten this

AND('[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!E5<>"instrument air",'[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!E5<>"steam")

Basically it is the part of the longer formula

IF('[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!E5="","",IF(AND('[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!E5<>"instrument air",'[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!E5<>"steam",'[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!J5<425,'[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!I5>40),'[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!B5,""))

 

[Asheesh]

Do you mind sharing a sample file with required output..

 

[NARAYANK991]

Hi ,

Try this :

=AND('[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!E5 <> {"instrument air", "steam"})

Narayan

 

[Jake Collins]

Dear gsvirdi

Can't you in the longer formula remove the '[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!E5<>"instrument air" and '[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!E5<>"steam" criteria since you already know that [Copy_BIB_Instrument List P0_20160530.xlsx]PI'!E5="",""

 

[gsvirdi]

Do you mind sharing a sample file with required output..

sample file attached

I'm trying to fetch a cell value based on two conditions using AND() but in the and statement I sometimes requires to use an OR conditions twice too.

 

[gsvirdi]

Dear gsvirdi

Can't you in the longer formula remove the '[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!E5<>"instrument air" and '[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!E5<>"steam" criteria since you already know that [Copy_BIB_Instrument List P0_20160530.xlsx]PI'!E5="",""

Dear Jake,
Pls note that it is the starting of IF() which ensures that I chk if the target cell is filled or not, if it is blank then the formulas should show blank, and quit without checking the next if statement having <> [/quote]IF([Copy_BIB_Instrument List P0_20160530.xlsx]PI'!E5="",""[/quote]

 

[gsvirdi]

Hi ,

Try this :

=AND('[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!E5 <> {"instrument air", "steam"})

Narayan

Thx Narayan, this seems to work, I'll now implement it in my 478592 entities and 87 conditions to see if I can make it a common formula in all sheets