Returning 1 of 4 values if conditions are met
 

Help, I'm trying to write a formula that will return different values based on different scenarios (i.e. if Condition 1 is met and condition 2 is met for Scenario A then return "1", etc.):

Scenario Condition 1 Condition 2 Then
A 1M <12M 1
B <1M <12M 2
C 1M 12M 3
D <1M 12M 4


Where condition 1 and 2 are text values not numeric.

My data sets look like this, "Value" is where I want to use a formula instead of manually entering:

Account A B Value
XXX 1M 12M 3
FFF 1M <12M 1
CCC 1M <12M 1
MMM <1M 12M 4
TTT <1M <12M 2
DDD 1M <12M 1
KKK 1M 12M 3
PPP <1M <12M 2

Help!

Trish

Returning 1 of 4 values if conditions are met
 

Hi Tricia,

Am Thu, 13 Feb 2014 08:28:13 -0800 (PST) schrieb Tricia Griffiths:

Account A B Value
XXX 1M 12M 3
FFF 1M <12M 1
CCC 1M <12M 1
MMM <1M 12M 4
TTT <1M <12M 2
DDD 1M <12M 1
KKK 1M 12M 3
PPP <1M <12M 2

try:
=(B2&C2="1M<12M")+(B2&C2="<1M<12M")*2+(B2&C2="1M 12M")*3+(B2&C2="<1M12M")*4


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Returning 1 of 4 values if conditions are met
 

Hi Tricia,

Am Thu, 13 Feb 2014 18:33:57 +0100 schrieb Claus Busch:

try:
=(B2&C2="1M<12M")+(B2&C2="<1M<12M")*2+(B2&C2="1M 12M")*3+(B2&C2="<1M12M")*4

or
=VLOOKUP(B2&C2,{"1M<12M",1;"<1M<12M",2;"1M12M", 3;"<1M12M",4},2,0)


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Returning 1 of 4 values if conditions are met
 

On Thursday, February 13, 2014 10:28:13 AM UTC-6, Tricia Griffiths wrote:
Help, I'm trying to write a formula that will return different values based on different scenarios (i.e. if Condition 1 is met and condition 2 is met for Scenario A then return "1", etc.):



Scenario Condition 1 Condition 2 Then

A 1M <12M 1

B <1M <12M 2

C 1M 12M 3

D <1M 12M 4





Where condition 1 and 2 are text values not numeric.



My data sets look like this, "Value" is where I want to use a formula instead of manually entering:



Account A B Value

XXX 1M 12M 3

FFF 1M <12M 1

CCC 1M <12M 1

MMM <1M 12M 4

TTT <1M <12M 2

DDD 1M <12M 1

KKK 1M 12M 3

PPP <1M <12M 2



Help!



Trish

=IF(R11,IF(S112,3,1),IF(S1<12,2,4))

Returning 1 of 4 values if conditions are met
 

Both of these worked! Thanks so much!!!

On Thursday, February 13, 2014 1:38:50 PM UTC-4, Claus Busch wrote:
Hi Tricia,



Am Thu, 13 Feb 2014 18:33:57 +0100 schrieb Claus Busch:



try:

=(B2&C2="1M<12M")+(B2&C2="<1M<12M")*2+(B2&C2="1M 12M")*3+(B2&C2="<1M12M")*4



or

=VLOOKUP(B2&C2,{"1M<12M",1;"<1M<12M",2;"1M12M", 3;"<1M12M",4},2,0)





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2