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

[Guest]

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.

 

[KL]

One way:

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

Regards,
KL

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[Blue Hornet]

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

 

[KL]

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(A4={"carrot","bean"}),"veg",IF(OR(A4={"bun","cake","donut"}),"pastry",A4)))

Regards,
KL

 

[Blue Hornet]

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]

KL

 

[Guest]

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

 

[Guest]

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