Returning 1 of 4 values if conditions are met

[Tricia Griffiths]

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

 

[Claus Busch]

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="<1M>12M")*4


Regards
Claus B.

 

[Claus Busch]

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="<1M>12M")*4

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


Regards
Claus B.

 

[Don Guillett]

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(R1>1,IF(S1>12,3,1),IF(S1<12,2,4))

 

[Tricia Griffiths]

Both of these worked! Thanks so much!!!

Hi Tricia,



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







or

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





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2