Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Impossible Formula!
This is a complicated need I have and the formula seems impossible but if there is a wizard out there who can tackle this I would greatly appreciate any help. Here is my quandry: I have one of five values in cell A1; "Red", "Blue", "Green","Yellow" or "Orange". I have one of two values in cell B1; "Chevy" or "Ford". If A1 contains "Red", "Blue" or "Green" and B1 contains "Chevy" I need C1 to automatically show the value 1000. If A1 contains "Red", "Blue" or "Green" and B1 contains "Ford" I need C1 to automatically show the value "2000." If A1 contains "Yellow" or "Orange" and B1 contains "Chevy" I need C1 to show the value "3000". If A1 contains "Yellow" or "Orange" and B1 contains "Ford" I need C1 to show the value "4000". Again, any help is appreciated. -- Filmmaker ------------------------------------------------------------------------ Filmmaker's Profile: http://www.excelforum.com/member.php...o&userid=16266 View this thread: http://www.excelforum.com/showthread...hreadid=276685 |
#2
|
|||
|
|||
Hi
not tested but try one way: =IF(OR(A1="Red",A1="Blue",A1="Green"),IF(B1="Chevy ",1000,IF(B1="Ford",2 000,"Not defined")),IF(OR(A1="Yellow",A1="Orange"),IF(B1="C hevy",3000,IF(B1="For d",4000,"Not defined")))) -- Regards Frank Kabel Frankfurt, Germany "Filmmaker" schrieb im Newsbeitrag ... This is a complicated need I have and the formula seems impossible but if there is a wizard out there who can tackle this I would greatly appreciate any help. Here is my quandry: I have one of five values in cell A1; "Red", "Blue", "Green","Yellow" or "Orange". I have one of two values in cell B1; "Chevy" or "Ford". If A1 contains "Red", "Blue" or "Green" and B1 contains "Chevy" I need C1 to automatically show the value 1000. If A1 contains "Red", "Blue" or "Green" and B1 contains "Ford" I need C1 to automatically show the value "2000." If A1 contains "Yellow" or "Orange" and B1 contains "Chevy" I need C1 to show the value "3000". If A1 contains "Yellow" or "Orange" and B1 contains "Ford" I need C1 to show the value "4000". Again, any help is appreciated. -- Filmmaker --------------------------------------------------------------------- --- Filmmaker's Profile: http://www.excelforum.com/member.php...o&userid=16266 View this thread: http://www.excelforum.com/showthread...hreadid=276685 |
#3
|
|||
|
|||
Filmmaker Wrote: This is a complicated need I have and the formula seems impossible but if there is a wizard out there who can tackle this I would greatly appreciate any help. Here is my quandry: I have one of five values in cell A1; "Red", "Blue", "Green","Yellow" or "Orange". I have one of two values in cell B1; "Chevy" or "Ford". If A1 contains "Red", "Blue" or "Green" and B1 contains "Chevy" I need C1 to automatically show the value 1000. If A1 contains "Red", "Blue" or "Green" and B1 contains "Ford" I need C1 to automatically show the value "2000." If A1 contains "Yellow" or "Orange" and B1 contains "Chevy" I need C1 to show the value "3000". If A1 contains "Yellow" or "Orange" and B1 contains "Ford" I need C1 to show the value "4000". Again, any help is appreciated. I have a formula that will help you ... it may not be elegant but it works (as I tested it): IF(AND(OR(A1="Red",A1="Blue",A1="Green"),B1="Chevy "),1000,IF(AND(OR(A1="Red",A1="Blue",A1="Green"),B 1="Ford"),2000,IF(AND(OR(A1="Yellow",A1="Orange"), B1="Chevy"),3000,IF(AND(OR(A1="Yellow",A1="Orange" ),B1="Ford"),4000,"")))) -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=276685 |
#4
|
|||
|
|||
Hi
Another one (I think it's easier to follow, and easier to expand when needed) =OR(A1="Red",A1="Blue","Green")*((B1="Chevy")*1000 +(B1="Ford")*2000)+OR(A1=" Yellow",A1="Orange")*((B1="Chevy")*3000+(B1="Ford" )*4000) Arvi Laanemets "Filmmaker" wrote in message ... This is a complicated need I have and the formula seems impossible but if there is a wizard out there who can tackle this I would greatly appreciate any help. Here is my quandry: I have one of five values in cell A1; "Red", "Blue", "Green","Yellow" or "Orange". I have one of two values in cell B1; "Chevy" or "Ford". If A1 contains "Red", "Blue" or "Green" and B1 contains "Chevy" I need C1 to automatically show the value 1000. If A1 contains "Red", "Blue" or "Green" and B1 contains "Ford" I need C1 to automatically show the value "2000." If A1 contains "Yellow" or "Orange" and B1 contains "Chevy" I need C1 to show the value "3000". If A1 contains "Yellow" or "Orange" and B1 contains "Ford" I need C1 to show the value "4000". Again, any help is appreciated. -- Filmmaker ------------------------------------------------------------------------ Filmmaker's Profile: http://www.excelforum.com/member.php...o&userid=16266 View this thread: http://www.excelforum.com/showthread...hreadid=276685 |
#5
|
|||
|
|||
Sorry, a typo!
=OR(A1="Red",A1="Blue","A1=Green")*((B1="Chevy")*1 000+(B1="Ford")*2000)+OR(A 1="Yellow",A1="Orange")*((B1="Chevy")*3000+(B1="Fo rd")*4000) Arvi Laanemets |
#6
|
|||
|
|||
=VLOOKUP(A1&B1,{"RedChevy",1000;"BlueChevy",1000;" GreenChevy",1000;"RedFord",2000;"BlueFord",2000;"G reenFord",2000;"YellowChevy",3000;"OrangeChevy",30 00;"YellowFord",4000;"OrangeFord",4000},2,0) Filmmaker Wrote: This is a complicated need I have and the formula seems impossible but if there is a wizard out there who can tackle this I would greatly appreciate any help. Here is my quandry: I have one of five values in cell A1; "Red", "Blue", "Green","Yellow" or "Orange". I have one of two values in cell B1; "Chevy" or "Ford". If A1 contains "Red", "Blue" or "Green" and B1 contains "Chevy" I need C1 to automatically show the value 1000. If A1 contains "Red", "Blue" or "Green" and B1 contains "Ford" I need C1 to automatically show the value "2000." If A1 contains "Yellow" or "Orange" and B1 contains "Chevy" I need C1 to show the value "3000". If A1 contains "Yellow" or "Orange" and B1 contains "Ford" I need C1 to show the value "4000". Again, any help is appreciated. -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=276685 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Simple formula doesn't quite add up | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) | |||
Paste is is copying in formula, but display is wrong. | Excel Discussion (Misc queries) | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) |