Review help

[adulted]

Hello all,

In helping a friend with an excel review , I came across a SIMPLE stumper. Any help would be appreciated. I have tried if(and, also if(or to no avail

Answers should reside in Column I

If the applicant is male, he is required to be between 170 cm and 210 cm inclusive, however if the applicant is female, she can be as much as 5 cm shorter than her male counterpart, yet the maximum height remains unchanged.

Excel Workbook
	A	B	C	D	E	F	G	H	I
39	Last Name	First Name	Gender	Age	Height	Health Status	Criminal Record * * * * * * * * *?	Pass Age Requirement * * * * * *?	Pass Height Requirement * * * * * * *?
40	*	*	*	*	*	*	*	*	*
41	Smith	John	M	16	176	1	N	FALSE	*
42	Smith	James	M	22	176	1	N	True	*
43	Smith	Joseph	M	25	174	3	N	True	*
44	Smith	Julie	F	21	180	2	Y	True	*
45	Smith	Jean	F	27	166	1	N	True	*
46	Smith	Judy	F	99	180	1	N	False	*
47	Smith	Jeffrey	M	32	180	1	N	True	*
Applicant Info

Not looking to do his assignment, just assisting him with his learning of nested functions
Thank you,
Paul

 

[sr12345]

Try
=if(or(and(c40="M",e40>=170,e40<=210),and(c40="F",e40>=165,e40<=205)),"True","False")

 

[adulted]

I am getting True on all instances. Looking at this data, visually there is data that should show False?

Scenario repeat:

If the applicant is male, he is required to be between 170 cm and 210 cm inclusive, however if the applicant is female, she can be as much as 5 cm shorter than her male counterpart, yet the maximum height remains unchanged.

Excel Workbook
	A	B	C
1	Gender	Height	Pass Height Requirement * * * * * * *?
2	*	*	*
3	M	176	True
4	M	176	True
5	M	174	True
6	F	180	True
7	F	166	True
8	F	180	True
9	M	180	True
Sheet1

Thanks again for this confusing but intersting formula.
Paul

 

[John Davis]

I am getting True on all instances. Looking at this data, visually there is data that should show False?

Scenario repeat:

If the applicant is male, he is required to be between 170 cm and 210 cm inclusive, however if the applicant is female, she can be as much as 5 cm shorter than her male counterpart, yet the maximum height remains unchanged.

Excel Workbook
	A	B	C
1	Gender	Height	Pass Height Requirement * * * * * * *?
2	*	*	*
3	M	176	True
4	M	176	True
5	M	174	True
6	F	180	True
7	F	166	True
8	F	180	True
9	M	180	True
Sheet1

Thanks again for this confusing but intersting formula.
Paul

Maybe?

=IF(AND(C6="M",E6>170,E6<210),"PASSED",IF(AND(C6="F",C6>165,C6<205),"PASSED","FAILED"))

 

[sr12345]

I am getting True on all instances. Looking at this data, visually there is data that should show False?


Paul

Based on the criteria you provided (170-210 or 165-205), all the height values fall into the acceptable range. To test the formula, replace one of the VLOOKUP height formulas with a hard figure that is out of range, such as 150, and see what the formula does.