Combining IF AND & FIND Functions

[Steve]

Hi all

I've been trying to solve this one for a week and am just getting nowhere,
any help would be greatly appreciated.

I have a list of shops in column A and a list of total sales figures in
column B, Some of the shops in Column A are chains and so have multiple
entries (think WH Smiths, Borders etc...). I'm trying to write a formula that
will check to see if the Characters WHS are in column A and see if the figure
in Column B is greater than 15000, and if so enter "Sales OK" otherwise enter
"NON Chain". The best I could come up with is
=if(and(find("WHS*",a2)b2>15000,"Sales OK","NON Chain")) but it doesn't work.
Help!!!!!

Hi

Try this:
=IF(AND(ISNUMBER(FIND("WHS",A2)),B2>15000),"Sales OK","NON Chain")
Please note that FIND is case-sensitive, so WHS must be in capitals.

Andy.


"Steve" <[email protected]> wrote in message
news:[email protected]...
> Hi all
>
> I've been trying to solve this one for a week and am just getting nowhere,
> any help would be greatly appreciated.
>
> I have a list of shops in column A and a list of total sales figures in
> column B, Some of the shops in Column A are chains and so have multiple
> entries (think WH Smiths, Borders etc...). I'm trying to write a formula
> that
> will check to see if the Characters WHS are in column A and see if the
> figure
> in Column B is greater than 15000, and if so enter "Sales OK" otherwise
> enter
> "NON Chain". The best I could come up with is
> =if(and(find("WHS*",a2)b2>15000,"Sales OK","NON Chain")) but it doesn't
> work.
> Help!!!!!

[Steve]

Thanks Andy, ISNUMBER hadn't entered my thoughts - that works

Steve

"Andy" wrote:

> Hi
>
> Try this:
> =IF(AND(ISNUMBER(FIND("WHS",A2)),B2>15000),"Sales OK","NON Chain")
> Please note that FIND is case-sensitive, so WHS must be in capitals.
>
> Andy.
>
>
> "Steve" <[email protected]> wrote in message
> news:[email protected]...
> > Hi all
> >
> > I've been trying to solve this one for a week and am just getting nowhere,
> > any help would be greatly appreciated.
> >
> > I have a list of shops in column A and a list of total sales figures in
> > column B, Some of the shops in Column A are chains and so have multiple
> > entries (think WH Smiths, Borders etc...). I'm trying to write a formula
> > that
> > will check to see if the Characters WHS are in column A and see if the
> > figure
> > in Column B is greater than 15000, and if so enter "Sales OK" otherwise
> > enter
> > "NON Chain". The best I could come up with is
> > =if(and(find("WHS*",a2)b2>15000,"Sales OK","NON Chain")) but it doesn't
> > work.
> > Help!!!!!
>
>
>

Thanks for the feedback!

Andy.

"Steve" <[email protected]> wrote in message
news:[email protected]...
> Thanks Andy, ISNUMBER hadn't entered my thoughts - that works
>
> Steve
>
> "Andy" wrote:
>
>> Hi
>>
>> Try this:
>> =IF(AND(ISNUMBER(FIND("WHS",A2)),B2>15000),"Sales OK","NON Chain")
>> Please note that FIND is case-sensitive, so WHS must be in capitals.
>>
>> Andy.
>>
>>
>> "Steve" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi all
>> >
>> > I've been trying to solve this one for a week and am just getting
>> > nowhere,
>> > any help would be greatly appreciated.
>> >
>> > I have a list of shops in column A and a list of total sales figures in
>> > column B, Some of the shops in Column A are chains and so have multiple
>> > entries (think WH Smiths, Borders etc...). I'm trying to write a
>> > formula
>> > that
>> > will check to see if the Characters WHS are in column A and see if the
>> > figure
>> > in Column B is greater than 15000, and if so enter "Sales OK" otherwise
>> > enter
>> > "NON Chain". The best I could come up with is
>> > =if(and(find("WHS*",a2)b2>15000,"Sales OK","NON Chain")) but it doesn't
>> > work.
>> > Help!!!!!
>>
>>
>>