Help! A "logical" question :)

C

CMIConnie

I think I am making this more difficult than it is, but I can't sort it out
in my mind. I need a formula that does the following:
Returns "ADK" if the ref cell contains Dean, dean, Dean's, dean's or returns
"DJM" if the same ref cell contains Dave, dave, Dave's, dave's, and returns
"MWP" if there is anything else but Dean or Dave or David in the cell. I'm
thinking that I can use the logic if it finds an e in the 2nd position,
return "ADK" or if it finds an a in the 2nd position then return "DJM". Am I
on the right track with this? It seems like there are too many logigals for
an if/then formula?
Any help would be GREATLY appreciated! (I am using Excel 2000)
 
N

Niek Otten

=IF(LOWER(LEFT(A1,4))="dean","ADK",IF(LOWER(LEFT(A1,4))="dave","DJM","MWP"))
 
B

Bill Martin

I think I am making this more difficult than it is, but I can't sort it out
in my mind. I need a formula that does the following:
Returns "ADK" if the ref cell contains Dean, dean, Dean's, dean's or returns
"DJM" if the same ref cell contains Dave, dave, Dave's, dave's, and returns
"MWP" if there is anything else but Dean or Dave or David in the cell. I'm
thinking that I can use the logic if it finds an e in the 2nd position,
return "ADK" or if it finds an a in the 2nd position then return "DJM". Am I
on the right track with this? It seems like there are too many logigals for
an if/then formula?
Any help would be GREATLY appreciated! (I am using Excel 2000)
Click to expand...

-------------------------

How about:

=if(ucase(left(A1,4))="DEAN","ADK",if(ucase(left(A1,4))="DAVE","DJM","MWP"))

Bill
 
N

Niek Otten

Hi Bill,

You can see I'm lazier than you are: I tried to avoid holding the SHIFT key
:)
 
B

Bill Martin

-------------------------

How about:

=if(ucase(left(A1,4))="DEAN","ADK",if(ucase(left(A1,4))="DAVE","DJM","MWP"))

Bill
Click to expand...

-------------------

Oops -- for a formula, replace the "ucase" with "upper". I'm confusing
formula words with VBA words. Sorry... (and why are they different
anyhow?)

Bill
 
R

Ron Coderre

If the cell can contain the specified text anywhere in the cell, like in the
following examples:
ADean
Ben dean bean
The Dean's list
hi Dave
to dave and ben
is this David?

Then, try this:
For text in A1
B1:
=CHOOSE(SUMPRODUCT(COUNTIF(A1,{"*dean*","*dav*"})*{1,2})+1,"MWP","ADK","DJM","Has both!")

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
J

JE McGimpsey

So should "David" return DJM, too (based on "anything else but Dean or
Dave or David")?

Should "Deanna" return MWP?

If so, this should work for you:

=IF(LEFT(SUBSTITUTE(A1&" ","'s",""),5)="dean ","ADK",
IF(OR(LEFT(SUBSTITUTE(A1&" ","'s",""),5)="dave ",A1="David"),
"DJM","JWM"))
 
J

JE McGimpsey

Just a note - the UCASE()'s are unnecessary in this formula since XL's
comparisons are case insensitive.

See my other post for a different answer - the OP's problem statement
seemed rather ambiguous.
 
C

CMIConnie

Thank you for your reply! It steered me in the right direction and saved me
oodles of time!
 
C

CMIConnie

Thank you for your reply! It steered me in the right direction and saved me
oodles of time!
 
C

CMIConnie

Thank you... I will try it :)

Ron Coderre said:
If the cell can contain the specified text anywhere in the cell, like in the
following examples:
ADean
Ben dean bean
The Dean's list
hi Dave
to dave and ben
is this David?

Then, try this:
For text in A1
B1:
=CHOOSE(SUMPRODUCT(COUNTIF(A1,{"*dean*","*dav*"})*{1,2})+1,"MWP","ADK","DJM","Has both!")

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
Click to expand...
 
N

Niek Otten

Absolutely right, JE!

Thanks,

Niek

JE McGimpsey said:
Just a note - the UCASE()'s are unnecessary in this formula since XL's
comparisons are case insensitive.

See my other post for a different answer - the OP's problem statement
seemed rather ambiguous.
Click to expand...
 
Top