Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default Nested if with "wild card"

Would really appreciate it if you can help with the following nested if
example:

Example:

A1 B1
Large 2009 100
Medium 2009 200
Medium 2008 500
Large 2008 300
Other 2009 400

I want to set up a nested if formula to pick up the value in B1 if there is
the word
"Large" in A1 or if there's "Medium" in A1, else enter zero.

I was going with this but it didn't work:
=if(or(A1="Large*",A1="Medium*"),B1,0)

Can someone help? Thank you.






  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 303
Default Nested if with "wild card"

something like

=IF(OR(LEFT(A1,4)="Larg",LEFT(A1,4)="medi"),B1,0)


--
Greetings from New Zealand

"mldancing" wrote in message
...
Would really appreciate it if you can help with the following nested if
example:

Example:

A1 B1
Large 2009 100
Medium 2009 200
Medium 2008 500
Large 2008 300
Other 2009 400

I want to set up a nested if formula to pick up the value in B1 if there
is
the word
"Large" in A1 or if there's "Medium" in A1, else enter zero.

I was going with this but it didn't work:
=if(or(A1="Large*",A1="Medium*"),B1,0)

Can someone help? Thank you.








  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default Nested if with "wild card"

Thank you for you reply. In this example this formula would work.

Is it possible to use a wild card? 'cause there could be other combination
of description
such as Extra Large or Medium Small.

Thank you.

"Bill Kuunders" wrote:

something like

=IF(OR(LEFT(A1,4)="Larg",LEFT(A1,4)="medi"),B1,0)


--
Greetings from New Zealand

"mldancing" wrote in message
...
Would really appreciate it if you can help with the following nested if
example:

Example:

A1 B1
Large 2009 100
Medium 2009 200
Medium 2008 500
Large 2008 300
Other 2009 400

I want to set up a nested if formula to pick up the value in B1 if there
is
the word
"Large" in A1 or if there's "Medium" in A1, else enter zero.

I was going with this but it didn't work:
=if(or(A1="Large*",A1="Medium*"),B1,0)

Can someone help? Thank you.









  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Nested if with "wild card"


You can't use a wildcard with comparison operators like =, try using
COUNTIF

=IF(SUM(COUNTIF(A1,{"*large*","*medium*"})),B1,"")

regards, barry


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=135071

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Nested if with "wild card"

Bill's formula only looks at the first 4 characters in the cell (=left(a1,4)).
So you don't need a wild card.

mldancing wrote:

Thank you for you reply. In this example this formula would work.

Is it possible to use a wild card? 'cause there could be other combination
of description
such as Extra Large or Medium Small.

Thank you.

"Bill Kuunders" wrote:

something like

=IF(OR(LEFT(A1,4)="Larg",LEFT(A1,4)="medi"),B1,0)


--
Greetings from New Zealand

"mldancing" wrote in message
...
Would really appreciate it if you can help with the following nested if
example:

Example:

A1 B1
Large 2009 100
Medium 2009 200
Medium 2008 500
Large 2008 300
Other 2009 400

I want to set up a nested if formula to pick up the value in B1 if there
is
the word
"Large" in A1 or if there's "Medium" in A1, else enter zero.

I was going with this but it didn't work:
=if(or(A1="Large*",A1="Medium*"),B1,0)

Can someone help? Thank you.










--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
change "true" and "false" to "availble" and "out of stock" inthestands Excel Worksheet Functions 2 July 19th 07 07:05 PM
trying to do custom autofilter but its not recognising "*"as wild max power Excel Discussion (Misc queries) 1 August 18th 06 01:20 AM
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next BCB New Users to Excel 7 May 13th 06 10:02 PM
Wild Characters in "IF" function jakar Excel Discussion (Misc queries) 3 November 17th 05 06:35 PM


All times are GMT +1. The time now is 11:26 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"