HD I build funct @if cell=AorBorC then ...else if cell=DorE then
 

Does anyone know how to build a formula as follows

if(a4='apple' or 'pear' or 'plum', then 'fruit' else
if(a4='carrot' or 'bean', then 'veg' else
if(a4='bun' or 'cake' or 'donut' then 'pastry' else a4)))

I have been unable to find satisfactory info re or & and as part of
functions.

One way:

=CHOOSE(OR(A4={"apple","pear","plum"})+OR(A4={"car rot","bean"})*2+OR(A4={"bun","cake","donut"})*3+1, A4,"fruit","veg","pastry")

Regards,
KL



"Jocko_MacDuff116" wrote in
message ...
Does anyone know how to build a formula as follows

if(a4='apple' or 'pear' or 'plum', then 'fruit' else
if(a4='carrot' or 'bean', then 'veg' else
if(a4='bun' or 'cake' or 'donut' then 'pastry' else a4)))

I have been unable to find satisfactory info re or & and as part of
functions.

Another way is to use VLOOKUP and build a table with all the possible inputs
and then map against it the required outputs, eg

Apple Fruit
Pear Fruit
Plum Fruit
Carrot Veg
Bean Veg
Bun Pastry
Cake Pastry
Donut Pastry

usually easier to maintain depending on list sizes

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"KL" wrote in message
...
One way:

=CHOOSE(OR(A4={"apple","pear","plum"})+OR(A4={"car rot","bean"})*2+OR(A4={"bun","cake","donut"})*3+1, A4,"fruit","veg","pastry")

Regards,
KL



"Jocko_MacDuff116" wrote in
message ...
Does anyone know how to build a formula as follows

if(a4='apple' or 'pear' or 'plum', then 'fruit' else
if(a4='carrot' or 'bean', then 'veg' else
if(a4='bun' or 'cake' or 'donut' then 'pastry' else a4)))

I have been unable to find satisfactory info re or & and as part
of
functions.

I'm with Ken Wright and his answer below, but you did express a wish to
understand the use of "AND", "OR" and (though you didn't say it) "NOT"
in IF statements. So ...

=IF( OR( A4 = "apple", A4 = "pear", A4 = "plum"), "fruit", IF( OR( A4 =
"carrot", A4 = "bean"), "veg", IF( OR( A4 = "bun", A4 = "cake", A4 =
"donut"), "pastry", "who knows?")))

To use AND you'd use the same general construct:
=if ( and( A4 = "apple", B4 = "fruit"), "apple fruit", "not apple
fruit")

NOT works the same.

Chris

Hi Blue Hornet,

I geuss, your formula could be slightly more efficient if you used the fixed
arrays:

=IF(OR(A4={"apple","pear","plum"}),"fruit",IF(OR(A 4={"carrot","bean"}),"veg",IF(OR(A4={"bun","cake", "donut"}),"pastry",A4)))

Regards,
KL

"Blue Hornet" wrote in message
oups.com...
I'm with Ken Wright and his answer below, but you did express a wish to
understand the use of "AND", "OR" and (though you didn't say it) "NOT"
in IF statements. So ...

=IF( OR( A4 = "apple", A4 = "pear", A4 = "plum"), "fruit", IF( OR( A4 =
"carrot", A4 = "bean"), "veg", IF( OR( A4 = "bun", A4 = "cake", A4 =
"donut"), "pastry", "who knows?")))

To use AND you'd use the same general construct:
=if ( and( A4 = "apple", B4 = "fruit"), "apple fruit", "not apple
fruit")

NOT works the same.

Chris

Thanks, KL. I don't do the fixed array thing, so that's good to know.
I was just trying to help Jocko with what seemed to be his underlying
question about how to write the =IF( OR( A, B), C, D) formula in the
first place. I recall not understanding that when I started with
Excel, too. (And then in VBA it's back to
If A or B then
C
Else
D
End If

Go figure.

Chris

:-)

KL


"Blue Hornet" wrote in message
oups.com...
Thanks, KL. I don't do the fixed array thing, so that's good to know.
I was just trying to help Jocko with what seemed to be his underlying
question about how to write the =IF( OR( A, B), C, D) formula in the
first place. I recall not understanding that when I started with
Excel, too. (And then in VBA it's back to
If A or B then
C
Else
D
End If

Go figure.

Chris

Thanks for your help. I've really been struggling with this one.

"Blue Hornet" wrote:

I'm with Ken Wright and his answer below, but you did express a wish to
understand the use of "AND", "OR" and (though you didn't say it) "NOT"
in IF statements. So ...

=IF( OR( A4 = "apple", A4 = "pear", A4 = "plum"), "fruit", IF( OR( A4 =
"carrot", A4 = "bean"), "veg", IF( OR( A4 = "bun", A4 = "cake", A4 =
"donut"), "pastry", "who knows?")))

To use AND you'd use the same general construct:
=if ( and( A4 = "apple", B4 = "fruit"), "apple fruit", "not apple
fruit")

NOT works the same.

Chris

Thanks for your help. I've really been struggling with this one.

"KL" wrote:

One way:

=CHOOSE(OR(A4={"apple","pear","plum"})+OR(A4={"car rot","bean"})*2+OR(A4={"bun","cake","donut"})*3+1, A4,"fruit","veg","pastry")

Regards,
KL



"Jocko_MacDuff116" wrote in
message ...
Does anyone know how to build a formula as follows

if(a4='apple' or 'pear' or 'plum', then 'fruit' else
if(a4='carrot' or 'bean', then 'veg' else
if(a4='bun' or 'cake' or 'donut' then 'pastry' else a4)))

I have been unable to find satisfactory info re or & and as part of
functions.