Combine Formula

[Johnboy28]

Hi,

Is it possible to combine the below into one formula?

If Room 1 - Receival & Room 2 - Receival equal 0 Return "In Transit"
If cell equals Receival 1 & Entry 1 Return "Processed" & If cell equals Receival 2 & Entry 2 Return "Processed"

Room 1 - Receival	Room 1 - Entry	Room 2 - Receival	Room 2 - Entry	Location
0	0	0	0	???
Receival 1	Entry 1	0	0
0	Entry 1	0	0
0	Entry 1	Receival 2	Entry 2
0	0	0	0
Receival 1	Entry 1	Receival 2	Entry 2
Receival 1	Entry 1	0	0
Receival 1	Entry 1	Receival 2	Entry 2
0	0	0	0

 

[Johnboy28]

Updated
If Room 1 - Receival & Room 2 - Receival equal 0 Return "In Transit"
If cell equals Receival 1 & Entry 1 Return "Processed" & If cell equals Receival 2 & Entry 2 Return "Processed" & If cell equals Receival 1 & Receival 2 with no Entry retuen "In Chiller"

Room 1 - Receival	Room 1 - Entry	Room 2 - Receival	Room 2 - Entry	Location
0	0	0	0	???
Receival 1	Entry 1	0	0
0	Entry 1	0	0
0	Entry 1	Receival 2	Entry 2
0	0	0	0
Receival 1	Entry 1	Receival 2	Entry 2
Receival 1	Entry 1	0	0
Receival 1	Entry 1	Receival 2	Entry 2
Receival 1	0	0	0
0	0	Receival 2	0
0	0	0	0

 

[kevin9999]

Deleted - new condition added after I posted...

 

[Michael M]

Maybe...UNTESTED

=IF(AND(A2=0,C2=0),"In Transit",IF(A2=B2,"Processed",IF(C2=D2,"Processed","")))

 

[awoohaw]

Try this:

Mr excel questions 59.xlsm
	A	B	C	D	E
1	Room 1 - Receival	Room 1 - Entry	Room 2 - Receival	Room 2 - Entry	Location
2	0	0	0	0	In Transit
3	Receival 1	Entry 1	0	0	Processed
4	0	Entry 1	0	0	In Transit
5	0	Entry 1	Receival 2	Entry 2	Unknown
6	0	0	0	0	In Transit
7	Receival 1	Entry 1	Receival 2	Entry 2	Processed
8	Receival 1	Entry 1	0	0	Processed
9	Receival 1	Entry 1	Receival 2	Entry 2	Processed
10	0	0	0	0	In Transit
JohnBoy28

Cell Formulas
Range	Formula
E2:E10	E2	=IF(AND($A2=0,$C2=0),"In Transit", IF(AND($A2="Receival 1",$B2="Entry 1"),"Processed", IF(AND($C2="Retreival 2",$D2="Entry 2"),"Processed","Unknown")))

 

[kevin9999]

Try this:

Mr excel questions 59.xlsm
	A	B	C	D	E
1	Room 1 - Receival	Room 1 - Entry	Room 2 - Receival	Room 2 - Entry	Location
2	0	0	0	0	In Transit
3	Receival 1	Entry 1	0	0	Processed
4	0	Entry 1	0	0	In Transit
5	0	Entry 1	Receival 2	Entry 2	Unknown
6	0	0	0	0	In Transit
7	Receival 1	Entry 1	Receival 2	Entry 2	Processed
8	Receival 1	Entry 1	0	0	Processed
9	Receival 1	Entry 1	Receival 2	Entry 2	Processed
10	0	0	0	0	In Transit
JohnBoy28

Cell Formulas
Range	Formula
E2:E10	E2	=IF(AND($A2=0,$C2=0),"In Transit", IF(AND($A2="Receival 1",$B2="Entry 1"),"Processed", IF(AND($C2="Retreival 2",$D2="Entry 2"),"Processed","Unknown")))

@awoohaw I think you did the same as me - worked out solution while the OP was in the process of adding another condition...

 

[awoohaw]

Okay, try this:

Mr excel questions 59.xlsm
	A	B	C	D	E
1	Room 1 - Receival	Room 1 - Entry	Room 2 - Receival	Room 2 - Entry	Location
2	0	0	0	0	In Transit
3	Receival 1	Entry 1	0	0	Processed
4	0	Entry 1	0	0	In Transit
5	0	Entry 1	Receival 2	Entry 2	Unknown
6	0	0	0	0	In Transit
7	Receival 1	Entry 1	Receival 2	Entry 2	Processed
8	Receival 1	Entry 1	0	0	Processed
9	Receival 1		Receival 2		In Chiller
10	0	0	0	0	In Transit
JohnBoy28

Cell Formulas
Range	Formula
E2:E10	E2	=IF(AND($A2=0,$C2=0),"In Transit", IF(AND($A2="Receival 1",$B2="Entry 1"),"Processed", IF(AND($C2="Retreival 2",$D2="Entry 2"),"Processed", IF(AND($A2="Receival 1",$C2="Receival 2"),"In Chiller", "Unknown" ))))

 

[Johnboy28]

Okay, try this:

Mr excel questions 59.xlsm
	A	B	C	D	E
1	Room 1 - Receival	Room 1 - Entry	Room 2 - Receival	Room 2 - Entry	Location
2	0	0	0	0	In Transit
3	Receival 1	Entry 1	0	0	Processed
4	0	Entry 1	0	0	In Transit
5	0	Entry 1	Receival 2	Entry 2	Unknown
6	0	0	0	0	In Transit
7	Receival 1	Entry 1	Receival 2	Entry 2	Processed
8	Receival 1	Entry 1	0	0	Processed
9	Receival 1		Receival 2		In Chiller
10	0	0	0	0	In Transit
JohnBoy28

Cell Formulas
Range	Formula
E2:E10	E2	=IF(AND($A2=0,$C2=0),"In Transit", IF(AND($A2="Receival 1",$B2="Entry 1"),"Processed", IF(AND($C2="Retreival 2",$D2="Entry 2"),"Processed", IF(AND($A2="Receival 1",$C2="Receival 2"),"In Chiller", "Unknown" ))))

Thanks for your help. The formula work good. Can you please change so if Receival 1 or Receival 2 is present even by just receival 1 will show "In chiller" Sample below.

Room 1 - Receival	Room 1 - Entry	Room 2 - Receival	Room 2 - Entry	Location
Receival 1	0	0	0	In chiller
0	0	Receival 2	0	In Chiller

 

[awoohaw]

change the AND in the 4th IF to an OR.

My pleasure. =IF(AND($A2=0,$C2=0),"In Transit", IF(AND($A2="Receival 1",$B2="Entry 1"),"Processed", IF(AND($C2="Retreival 2",$D2="Entry 2"),"Processed", IF(OR($A2="Receival 1",$C2="Receival 2"),"In Chiller", "Unknown" )))) Best Wishes!

 

[Johnboy28]

change the AND in the 4th IF to an OR.

My pleasure. =IF(AND($A2=0,$C2=0),"In Transit", IF(AND($A2="Receival 1",$B2="Entry 1"),"Processed", IF(AND($C2="Retreival 2",$D2="Entry 2"),"Processed", IF(OR($A2="Receival 1",$C2="Receival 2"),"In Chiller", "Unknown" )))) Best Wishes!

Perfect - Thank you. Appreciate it.