Combinging IF Statements??

[mcintoshmc]

I know there is any easier way of doing this by combing if(and(or statements, but I did it the long way. How can I combine all of these IF Statements to 1 formula?

IF((AND($A41=1,$B41=0,$C41=0,$D41=0,$F41=0,$G41=0,$H41=0,$I41=0,$J41=0)),"Step 1","")
IF((AND($A41=1,$B41=2,$C41=0,$D41=0,$F41=0,$G41=0,$H41=0,$I41=0,$J41=0)),"Step 2","")
IF((AND($A41=1,$B41=2,$C41=3,$D41=0,$F41=0,$G41=0,$H41=0,$I41=0,$J41=0)),"Step 3","")
IF((AND($A41=1,$B41=2,$C41=3,$D41=4,$F41=0,$G41=0,$H41=0,$I41=0,$J41=0)),"Step 4","")
IF((AND($A41=1,$B41=2,$C41=3,$D41=4,$F41=5,$G41=0,$H41=0,$I41=0,$J41=0)),"Step 5","")
IF((AND($A41=1,$B41=2,$C41=3,$D41=4,$F41=5,$G41=6,$H41=0,$I41=0,$J41=0)),"Step 6","")
IF((AND($A41=1,$B41=2,$C41=3,$D41=4,$F41=5,$G41=6,$H41=7,$I41=0,$J41=0)),"Step 7","")
IF((AND($A41=1,$B41=2,$C41=3,$D41=4,$F41=5,$G41=6,$H41=7,$I41=8,$J41=0)),"Step 8","")
IF((AND($A41=1,$B41=2,$C41=3,$D41=4,$F41=5,$G41=6,$H41=7,$I41=8,$J41=9)),"Step 9","")
IF((AND($A41=1,$B41=2,$C41=3,$D41=4,$F41=5,$G41=6,$H41=7,$I41=8,$J41=9,$K41=10)),"Step 10","")
IF((AND($A41=1,$B41=2,$C41=3,$D41=4,$F41=5,$G41=6,$H41=7,$I41=8,$J41=9,$K41=10,$L41="i9")),"Hired","")

 

[thisoldman]

I think this might work.

=IF(A41 <> 1, "", IF(MAX($A41:$K41) < 9, "Step " & MAX($A41:$K41), IF(AND(MAX($K41:$L41)=10, $L41="i9"), "Hired", "Step " & MAX(K41:L41))))

 

[mcintoshmc]

Re: Combining IF Statements??

I think this might work.

=IF(A41 <> 1, "", IF(MAX($A41:$K41) < 9, "Step " & MAX($A41:$K41), IF(AND(MAX($K41:$L41)=10, $L41="i9"), "Hired", "Step " & MAX(K41:L41))))

That is amazing. I wish I could sit down with you and break down how that works. I'm definitely going to study it, and use a little google as well. Thanks!

 

[jtakw]

Re: Combining IF Statements??

Hi,

Would this modified shorter version of thisoldman's formula work as well (M41), and in case you have numbers in Column E that is irrelevant to the result and may "throw" the result off (N41):

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
41	1	0	0	0		0	0	0	0	0	0		Step 1	Step 1
42	1	2	0	0		0	0	0	0	0	0		Step 2	Step 2
43	1	2	3	4		5	6	7	8	9	0		Step 9	Step 9
44	1	2	3	4		5	6	7	8	9	10		Step 10	Step 10
45	1	2	3	4		5	6	7	8	9	10	i9	Hired	Hired
Sheet1

Cell Formulas
Range	Formula
M41	=IF(A41<>1,"",IF(AND(MAX($A41:$K41)=10,$L41="i9"),"Hired","Step "&MAX($A41:$K41)))
N41	=IF(A41<>1,"",IF(AND(MAX($A41:$D41,$F41:$K41)=10,$L41="i9"),"Hired","Step "&MAX($A41:$D41,$F41:$K41)))

Formulae copied down.

 

[mcintoshmc]

Re: Combining IF Statements??

Hi,

Would this modified shorter version of thisoldman's formula work as well (M41), and in case you have numbers in Column E that is irrelevant to the result and may "throw" the result off (N41):

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
41	1	0	0	0		0	0	0	0	0	0		Step 1	Step 1
42	1	2	0	0		0	0	0	0	0	0		Step 2	Step 2
43	1	2	3	4		5	6	7	8	9	0		Step 9	Step 9
44	1	2	3	4		5	6	7	8	9	10		Step 10	Step 10
45	1	2	3	4		5	6	7	8	9	10	i9	Hired	Hired
Sheet1

Cell Formulas
Range	Formula
M41	=IF(A41<>1,"",IF(AND(MAX($A41:$K41)=10,$L41="i9"),"Hired","Step "&MAX($A41:$K41)))
N41	=IF(A41<>1,"",IF(AND(MAX($A41:$D41,$F41:$K41)=10,$L41="i9"),"Hired","Step "&MAX($A41:$D41,$F41:$K41)))

Formulae copied down.

Thisoldman's formula worked. I tried yours, and it just returned a blank cell. That said I wonder why it won't sort from A to Z correctly. It goes from step 6,5,4,3,2,10 when sorting from Z to A, and when going to A to Z it all goes blank.

 

[mcintoshmc]

I think this might work.

=IF(A41 <> 1, "", IF(MAX($A41:$K41) < 9, "Step " & MAX($A41:$K41), IF(AND(MAX($K41:$L41)=10, $L41="i9"), "Hired", "Step " & MAX(K41:L41))))

This formula works great. Now that I'm using it, I'm seeing that there needs to be a tweak. If I put a date in K41, it will change it to step 10 even though, they may really only be on step 4. How can we make it only reflect Step 10 if step 9 has been completed. The date in K41 is the "start date", so we will always have a start date prior to all the steps being completed.

 

[thisoldman]

Try this.

=IF(A41<>1,"",
  IF($J41 <> 9, "Step " & MAX($A41:$D41, $F41:$I41),
    IF($K41 = 10, IF($L41 = "i9", "Hired", "Step 10"), "Step 9")))

Written as a single line formula with no spaces, it looks like this.

=IF(A41<>1,"",IF($J41<>9,"Step"&MAX($A41:$D41,$F41:$I41),IF($K41=10,IF($L41="i9","Hired","Step10"),"Step9")))

 

[mcintoshmc]

Try this.

=IF(A41<>1,"",
  IF($J41 <> 9, "Step " & MAX($A41:$D41, $F41:$I41),
    IF($K41 = 10, IF($L41 = "i9", "Hired", "Step 10"), "Step 9")))

Written as a single line formula with no spaces, it looks like this.

=IF(A41<>1,"",IF($J41<>9,"Step"&MAX($A41:$D41,$F41:$I41),IF($K41=10,IF($L41="i9","Hired","Step10"),"Step9")))

Worked like a charm! Thank you.