Prev: External Referance over Network
Next: Sum and multiply at the same time?
From: מיכאל (מיקי) אבידן on 7 Apr 2010 03:35
Some shorter formula:
=IF(ISERR(SEARCH("testing",A1))*ISERR(SEARCH("cold",A1))*ISERR(SEARCH("working",A1))=0,"Yes","No")
Micky


"JLatham" wrote:

> Oops, turns out to work better if you use SEARCH instead:
>
> =IF(OR(IF(ISERR(SEARCH("testing",A3)),FALSE,TRUE),IF(ISERR(SEARCH("cold",A3)),FALSE,TRUE),IF(ISERR(SEARCH("working",A3)),FALSE,TRUE)),"Yes","No")
>
>
> "vinstream" wrote:
>
> > Hi,
> >
> > I have the following sample data in column A.
> >
> > germany (testing)
> > Germany (cold)
> > Germany
> > Austria (Testing)
> > Austria (cold)
> > Austria (working)
> > Austria (other)
> > China
> > China (alternate)
> >
> >
> > In column B, I want it to show that if the cell to the left has any of
> > the words 'testing' or 'cold' or 'working' it should show "yes" in
> > Column B else show "no".
> >
> > Is this possible?
> >
> >
> >
> > Regards,
> >
> > Vinstream
> >
> >
> > .
> >
From: מיכאל (מיקי) אבידן on 7 Apr 2010 03:40
As you can learn from my solution you don't need more than one IF statement.
Basically:
Up to "Excel 2003" (incl.) you can nest 7 IF Statements.
From "Excel 2007" you can nest 64 IF Statements.
Usually, no one nests that many statements - not even 7
In such cases one uses a small table with all the possibilities and returns
the result with VLOOKUP.
Micky


"vinstream" wrote:

> Thanks a ton...this is working.
>
> How many If statements can I use in one formula?
>
>
>
>
>
>
> On Apr 7, 11:12 am, "ozgrid.com" <d...(a)ozgrid.com> wrote:
> > Hello,
> >
> > Try;
> > =IF(OR(ISNUMBER(SEARCH("testing",A1)),ISNUMBER(SEARCH("cold",A1)),ISNUMBER(­SEARCH("working",A1))),"Yes","No")"vinstream" <vinstr...(a)gmail.com> wrote in message
> >
> > news:4fa4b07e-5507-4979-8d09-811cb70ad35e(a)s9g2000yqa.googlegroups.com...
> >
> >
> >
> > > Hi,
> >
> > > I have the following sample data in column A.
> >
> > > germany (testing)
> > > Germany (cold)
> > > Germany
> > > Austria (Testing)
> > > Austria (cold)
> > > Austria (working)
> > > Austria (other)
> > > China
> > > China (alternate)
> >
> > > In column B, I want it to show that if the cell to the left has any of
> > > the words 'testing' or 'cold' or 'working' it should show "yes" in
> > > Column B else show "no".
> >
> > > Is this possible?
> >
> > > Regards,
> >
> > > Vinstream- Hide quoted text -
> >
> > - Show quoted text -
>
> .
>
 | 
Pages: 1
Prev: External Referance over Network
Next: Sum and multiply at the same time?