Return values based on multiple cells

[SaraWitch]

Hello peeps,

I am trying to return a value based on multiple cells, and my formula is not working:

¦ MrExcel Queries.xlsm
	A	B	C	D	E	F
1	Gender	Pregnant	Adults	Child	Household Code	Should be
2	Female	No	1	0	#VALUE!	SF
3	Male	No	1	0	#VALUE!	SM
4	Female	Yes	1	0	#VALUE!	pregSF
5	Female	Yes	2	0	#VALUE!	pregC
6	Male	No	1	2	#VALUE!	SM+
7	Female	No	1	2	#VALUE!	SF+
8	Female	Yes	1	2	#VALUE!	SF+
9	Male	No	2	0	0	C
10	Female	No	2	0	0	C
11	Female	No	2	1	#VALUE!	F
12	Male	No	2	2	#VALUE!	F
Return Multiple Values

Cell Formulas
Range	Formula
E2:E12	E2	=IF(AND(C2=1,A2="Male"),"SM")*IF(AND(C2=1,A2="Female"),"SF")*IF(AND(C2=1,A2="Female",D2>0),"SF+")*IF(AND(C2=1,A2="Male",D2>0),"SM+")*IF(AND(C2>1,D2>0),"F")*IF(AND(C2>1,B2="Yes"),"pregC")*IF(AND(C2=1,B2="Yes"),"pregSF")

Any help would be appreciated

 

[etaf]

why )* between nested IF

you dont have a false condition at the end - so will get FALSE

=IF(AND(C2=1,A2="Male"),"SM",IF(AND(C2=1,A2="Female"),"SF",IF(AND(C2=1,A2="Female",D2>0),"SF+",IF(AND(C2=1,A2="Male",D2>0),"SM+",IF(AND(C2>1,D2>0),"F",IF(AND(C2>1,B2="Yes"),"pregC",IF(AND(C2=1,B2="Yes"),"pregSF")))))))

where is C
Male - NO

Book11
	A	B	C	D	E	F
1	Gender	Pregnant	Adults	Child	Household Code	Should be
2	Female	No	1	0	SF	SF
3	Male	No	1	0	SM	SM
4	Female	Yes	1	0	SF	pregSF
5	Female	Yes	2	0	pregC	pregC
6	Male	No	1	2	SM	SM+
7	Female	No	1	2	SF	SF+
8	Female	Yes	1	2	SF	SF+
9	Male	No	2	0	FALSE	C
10	Female	No	2	0	FALSE	C
11	Female	No	2	1	F	F
12	Male	No	2	2	F	F
Sheet1

Cell Formulas
Range	Formula
E2:E12	E2	=IF(AND(C2=1,A2="Male"),"SM",IF(AND(C2=1,A2="Female"),"SF",IF(AND(C2=1,A2="Female",D2>0),"SF+",IF(AND(C2=1,A2="Male",D2>0),"SM+",IF(AND(C2>1,D2>0),"F",IF(AND(C2>1,B2="Yes"),"pregC",IF(AND(C2=1,B2="Yes"),"pregSF")))))))

a few differences , in the results - may need investigating further

for example
,IF(AND(C2=1,A2="Female"),"SF",IF(AND(C2=1,A2="Female",D2>0)

because D is not tested in first AND , C2=1 and A2="female" will always give SF as D2 is not considered in the first IF - and so SF+ will never be considered

nested if work left to right

 

[skillilea]

Here is another approach may be easier to track the formula

=IFS(AND($A8="Female", $B8= "No", $C8 =1, $D8 =0), "SF",
AND($A8="Male", $B8= "No", $C8 =1, $D8 =0), "SM",
AND($A8="Female", $B8= "Yes", $C8 =1, $D8 =0), "pregSF",
AND($A8="Female", $B8= "Yes", $C8 =2, $D8 =0), "pregC",
AND($A8="Male", $B8= "No", $C8 =1, $D8 =2), "SM+",
TRUE,""
)

you need to build the rest of it out inbetween the last AND clause the the final TRUE condition

 

[SaraWitch]

Thank you, @etaf! You're right, I'd also forgotten to add criteria for "C", so adding this and reversing the formula as you suggested worked a treat!

¦ MrExcel Queries.xlsm
	A	B	C	D	E	F
1	Gender	Pregnant	Adults	Child	Household Code	Should be
2	Female	No	1	0	SF	SF
3	Male	No	1	0	SM	SM
4	Female	Yes	1	0	pregSF	pregSF
5	Female	Yes	2	0	pregC	pregC
6	Male	No	1	2	SM+	SM+
7	Female	No	1	2	SF+	SF+
8	Female	Yes	1	2	SF+	SF+
9	Male	No	2	0	C	C
10	Female	No	2	0	C	C
11	Female	No	2	1	F	F
12	Male	No	2	2	F	F
Return Multiple Values

Cell Formulas
Range	Formula
E2:E12	E2	=IF(AND(C2=1,A2="Female",D2>0),"SF+",IF(AND(C2=1,B2="Yes"),"pregSF",IF(AND(C2=1,A2="Female"),"SF",IF(AND(C2=1,A2="Male",D2>0),"SM+",IF(AND(C2=1,A2="Male"),"SM",IF(AND(C2>1,B2="Yes"),"pregC",IF(AND(C2>1,D2=0),"C",IF(AND(C2>1,D2>0),"F"))))))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F12	Expression	=F2<>E2	text	NO

 

[etaf]

you are welcome