Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
:-)
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 |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Build in Code | Excel Worksheet Functions | |||
How can I build a pivot table from multiple worksheets which are . | Excel Discussion (Misc queries) | |||
How would I build an "if" formula... | Excel Worksheet Functions | |||
How do I build a database of names and addresses? | Excel Discussion (Misc queries) | |||
How to Build an Excel-Report ? | Excel Discussion (Misc queries) |