"it uses more levels of nesting than are allowed in the current file format"

[mattbird]

I am trying to design a Body Composition Measurement table which works out a persons risk rating by combining Body Mass Index (BMI) and waist circumference (WC). I have been using an 'IF' formula (Excel 07, Win8) which was working well until I got the following message:

"it uses more levels of nesting than are allowed in the current file format"

To make it more complicated I need the two formulas below as one and for the cell to know the difference between Male 'M' and Female 'F' when entered into the gender cell (cell 'E') to give the correct result depending on sex. In the formula below H7 is BMI, I7 is the WC. Females and males work with the same BMI range but the WC is different as you will notes when looking a both formulas.

Does anyone know how to make this work?

Your help is greatly appreciated

Matt

Males
=IF(AND(H7<=18.5,I7<=102),"Increased Risk",
IF(AND(H7>=18.5,H7<24.9,I7<102),"No Increased Risk",IF(AND(H7>=18.5,H7<24.9,I7>=102),"Increased Risk",
IF(AND(H7>=25,H7<29.9,I7<94),"No Increased Risk",IF(AND(H7>=25,H7<29.9,I7>=94,I7<102),"Increased Risk",IF(AND(H7>=25,H7<29.9,I7>=102),"High Risk",
IF(AND(H7>=30,H7<34.9,I7<94),"Increased Risk",IF(AND(H7>=30,H7<34.9,I7>=94,I7<102),"High Risk",IF(AND(H7>=30,H7<34.9,I7>102),"Very High Risk",
IF(AND(H7>=35,H7<39.9,I7<94),"High Risk",IF(AND(H7>=35,H7<39.9,I7>=94,I7<102),"Very High Risk",IF(AND(H7>=35,H7<39.9,I7>102),"Extreme Risk",
IF(AND(H7>=40,I7<94),"Very High Risk",IF(AND(H7>=40,I7>=94),"Extreme Risk","No Result"))))))))))))))

Females
=IF(AND(H7<=18.5,I7<=88),"Increased Risk",
IF(AND(H7>=18.5,H7<24.9,I7<88),"No Increased Risk",IF(AND(H7>=18.5,H7<24.9,I7>=88),"Increased Risk",
IF(AND(H7>=25,H7<29.9,I7<80),"No Increased Risk",IF(AND(H7>=25,H7<29.9,I7>=80,I7<88),"Increased Risk",IF(AND(H7>=25,H7<29.9,I7>=88),"High Risk",
IF(AND(H7>=30,H7<34.9,I7<80),"Increased Risk",IF(AND(H7>=30,H7<34.9,I7>=80,I7<88),"High Risk",IF(AND(H7>=30,H7<34.9,I7>88),"Very High Risk",
IF(AND(H7>=35,H7<39.9,I7<80),"High Risk",IF(AND(H7>=35,H7<39.9,I7>=80,I7<88),"Very High Risk",IF(AND(H7>=35,H7<39.9,I7>88),"Extreme Risk",
IF(AND(H7>=40,I7<80),"Very High Risk",IF(AND(H7>=40,I7>=80),"Extreme Risk","No Result"))))))))))))))

 

[markmzz]

Markmzz,

Nearly working the LOOKUP, VLOOKUP and DATE work great the only thing is; again I have learned Excel 2003 only allows 3 Conditional Formats and I need 5.

Cell M2 need to show Increased Risk as yellow, No Increased Risk as illumine Green, High Risk as Orange, Very High Risk as Red and Extreme Risk as Maroon. Or can it be written into the formula ie the VLOOKUP to also copy the colour as well as the text in the cell?

Regards

Matt

I'm glad that the formulas are working.

About Conditional Formats, I don't know how to do that in Excel 2003 without code.

Maybe the link below can helps:

more than 3 conditional format excel 2003 - Microsoft Community

Markmzz

 

[mattbird]

Thanks for your help.

Matt

 

[oldbrewer]

A BIT LATE i KNOW BUT i SAW THIS AS A BIT OF A CHALLENGE

Table
Body Mass Index (BMI)
(calculated below)
		W1	W2	W3	M1	M2	M3
Waist Circumference		Woman < 80cm	Women 80 - 87.9cm	Women >= 88cm	Men < 94cm	Men 94 - 101.9cm	Men >= 102cm
Underweight	<18.5	No Increased Risk	Increased Risk	No Increased Risk	Increased Risk	Increased Risk	Increased Risk
Healthy Weight	18.5 - 24.9	No Increased Risk	No Increased Risk	No Increased Risk	No Increased Risk	No Increased Risk	Increased Risk
Overweight	25.0 - 29.9	Increased Risk	No Increased Risk	Increased Risk	No Increased Risk	Increased Risk	High Risk
Obese Class I	30.0 - 34.9	High Risk	Increased Risk	High Risk	Increased Risk	High Risk	Very High Risk
Obese Class II	35.0 - 39.9	High Risk	High Risk	Very High Risk	High Risk	Very High Risk	Extreme Risk
Obese Class III	>39.9999	Very High Risk	Very High Risk	Extreme Risk	Very High Risk	Extreme Risk	Extreme Risk
Gender	Height (cm)	Weight (Kg)	BMI	WC (cm)	BMI Catergory	Overall Health Risk	sex/waistcirc
M	163	80	30.1	93	Obese Class I	Increased Risk	M93
			XXX		XXX	***********
		THE TWO CELLS MARKED XXX ARE CALCULATED
		M93 IS A CONCATENATION OF SEX AND WAIST CIRCUMFERENCE
		THE LOOKUP TABLE BELOW IS USED TO RETURN THE APPROPRIATE HEALTH RISK FACTOR IN THE ROW ABOVE (MARKED *****)
		sex/waistcirc	UNDERWEIGHT	HEALTHYWEIGHT	OVERWEIGHT	OBESECLASS1	OBESECLASS2	OBESECLASS3
		M50	Increased Risk	No Increased Risk	No Increased Risk	Increased Risk	High Risk	Very High Risk
		M94	Increased Risk	No Increased Risk	Increased Risk	High Risk	Very High Risk	Extreme Risk
		M102	Increased Risk	Increased Risk	High Risk	Very High Risk	Extreme Risk	Extreme Risk
		M999	Increased Risk	Increased Risk	High Risk	Very High Risk	Extreme Risk	Extreme Risk
		W40	No Increased Risk	No Increased Risk	Increased Risk	High Risk	High Risk	Very High Risk
		W80	Increased Risk	No Increased Risk	No Increased Risk	Increased Risk	High Risk	Very High Risk
		W88	No Increased Risk	No Increased Risk	Increased Risk	High Risk	Very High Risk	Extreme Risk
		W999	No Increased Risk	No Increased Risk	Increased Risk	High Risk	Very High Risk	Extreme Risk

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[markmzz]

Thanks for your help.

Matt

You're welcome.

Markmzz