Thanks:  0
Likes:  0

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

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

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?

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"))))))))))))))

2. ## Re: "it uses more levels of nesting than are allowed in the current file format"

You could set up your numeric ranges and their categories in a VLOOKUP table rather than nesting IF statements.

00 Low
10 Mid
99 High

=VLOOKUP(12, A1:B3, 2) returns Mid

3. ## Re: "it uses more levels of nesting than are allowed in the current file format"

Mattbird, Good evening. I believe the best approach for your case is you build a table and then use the function vlookup.

Does a search much faster and your data will be organized in a clear way and very easily if you need to change.

You will see how easy it is to work with the function vlookup if their data is in table format.

4. ## Re: "it uses more levels of nesting than are allowed in the current file format"

Before I ask any further questions, just to let you know this is the first time I have try to do anything like this in Excel, so am quite new to it. please forgive my ignorance I am not sure what VLOOKUP is and how it works. I have had a look on line but am still unsure. I have copied the table I am trying to work off of below and a layout of the spread sheet I am trying to design, which I hope gives you a clear idea of what I am trying to do.

Table
 Body Mass Index (BMI) Waist Circumference Men < 94cm Men 94 - 101.9cm Men ≥ 102cm Woman < 80cm Women 80 - 87.9cm Women ≥ 88cm Underweight <18.5 Increased Risk Healthy Weight 18.5 - 24.9 No Increased Risk No Increased Risk Increased Risk Overweight 25.0 - 29.9 No Increased Risk Increased Risk High Risk Obese Class I 30.0 - 34.9 Increased Risk High Risk Very High Risk Obese Class II 35.0 - 39.9 High Risk Very High Risk Extreme Risk Obese Class III ≥40 Very High Risk Extreme Risk Extreme Risk

 Gender Hight (cm) Weight (Kg) BMI WC (cm) BMI Catergory Overall Health Risk M 163 80 30.1 93 Obese Class I Increased Risk

The overall health risk is where I would like it to say: No increased risk or increased risk or high risk etc depending on the BMI and WC.

Matt

5. ## Re: "it uses more levels of nesting than are allowed in the current file format"

Before I ask any further questions, just to let you know this is the first time I have try to do anything like this in Excel, so am quite new to it. please forgive my ignorance I am not sure what VLOOKUP is and how it works. I have had a look on line but am still unsure. I have copied the table I am trying to work off of below and a layout of the spread sheet I am trying to design, which I hope gives you a clear idea of what I am trying to do.

Table

Waist Circumference

Body Mass Index (BMI) Men < 94cm Men 94 - 102cm Men ≥ 102cm
Woman < 80cm Women 80 - 88cm Women ≥ 88cm

Underweight <18.5 Increased Risk Increased Risk Increased Risk

Health Weight 18.5 - 24.9 No Increased Risk No Increased Risk Increased Risk

Overweight 25.0 - 29.9 No Increased Risk Increased Risk High Risk

Obese Class I 30.0 - 34.9 Increased Risk High Risk Very High Risk

Obese Class II 35.0 - 39.9 High Risk Very High Risk Extreme Risk

Obese Class III ≥40 Very High Risk Extreme Risk Extreme Risk

 Gender Hight (cm) Weight (Kg) BMI WC (cm) BMI Catergory Overall Health Risk M 163 80 30.1 93 Obese Class I Increased Risk

The overall health risk is where I would like it to say: No increased risk or increased risk or high risk etc depending on the BMI and WC.

Matt

6. ## Re: "it uses more levels of nesting than are allowed in the current file format"

Still having issue trying to get the table on correctly... Sorry. It has worked this time.

Before I ask any further questions, just to let you know this is the first time I have try to do anything like this in Excel, so am quite new to it. please forgive my ignorance I am not sure what VLOOKUP is and how it works. I have had a look on line but am still unsure. I have copied the table I am trying to work off of below and a layout of the spread sheet I am trying to design, which I hope gives you a clear idea of what I am trying to do.

Table

 Waist Circumference Body Mass Index (BMI) Men < 94cm Men 94 - 101.9cm Men ≥ 102cm Woman < 80cm Women 80 - 87.9cm Women ≥ 88cm Underweight <18.5 Increased Risk Healthy Weight 18.5 - 24.9 No Increased Risk No Increased Risk Increased Risk Overweight 25.0 - 29.9 No Increased Risk Increased Risk High Risk Obese Class I 30.0 - 34.9 Increased Risk High Risk Very High Risk Obese Class II 35.0 - 39.9 High Risk Very High Risk Extreme Risk Obese Class III ≥40 Very High Risk Extreme Risk Extreme Risk

 Gender Hight (cm) Weight (Kg) BMI WC (cm) BMI Catergory Overall Health Risk M 163 80 30.1 93 Obese Class I Increased Risk

The overall health risk is where I would like it to say: No increased risk or increased risk or high risk etc depending on the BMI and WC.

Matt

7. ## Re: "it uses more levels of nesting than are allowed in the current file format"

mattbird, Good morning.

"...Before I ask any further questions, just to let you know this is the first time I have try to do anything like this in Excel, so am quite new to it..."
Everytime is a good time to start learning something new.

"...please forgive my ignorance I am not sure what VLOOKUP is and how it works. I have had a look on line but am still unsure..."
Don´t worry about it. We will help you understand VLOOKUP function in an easy and complete way.

8. ## Re: "it uses more levels of nesting than are allowed in the current file format"

I have uploaded my spread sheet to you hopefully this will give you a better idear. I will explain what I am trying to do but can't at the moment got to rush out.

9. ## Re: "it uses more levels of nesting than are allowed in the current file format"

Maybe this (with your table layout):

Layout

 Table Gender Hight (cm) Weight (Kg) BMI WC (cm) BMI Catergory Overall Health Risk Waist Circumference M 163 80 30,1 93 Obese Class I Increased Risk Body Mass Index (BMI) Men < 94cm Men 94 - 101,9cm Men ≥ 102cm F 163 80 30,1 93 Obese Class I Very High Risk Woman < 80cm Women 80 - 87,9cm Women ≥ 88cm Underweight <18,5 Increased Risk Healthy Weight 18,5 - 24,9 No Increased Risk No Increased Risk Increased Risk Overweight 25,0 - 29,9 No Increased Risk Increased Risk High Risk Obese Class I 30,0 - 34,9 Increased Risk High Risk Very High Risk Obese Class II 35,0 - 39,9 High Risk Very High Risk Extreme Risk Obese Class III ≥40 Very High Risk Extreme Risk Extreme Risk ********************* ********* ******************* ****************** ************** ** ******** ********* ********** **** ******** ************* *****************

Array formulas - use Ctrl+Shift+Enter and not only Enter to enter the formula

Code:
```In L2

=LOOKUP(\$J2,IFERROR(IF(RIGHT(\$B\$4:\$B\$9)="9",0.1,0)+RIGHT(\$B\$4:\$B\$9,4),0),\$A\$5:\$A\$10)

In M2

=VLOOKUP(\$L2,\$A\$5:\$E\$10,
MATCH(\$K2,IFERROR(IF(RIGHT(TRIM(SUBSTITUTE(OFFSET(\$B\$4:\$D\$4,-(\$G2="M"),),"cm","")),1)="9",0.1,0)+
RIGHT(SUBSTITUTE(SUBSTITUTE(OFFSET(\$B\$4:\$D\$4,-(\$G2="M"),),"cm","")," ",REPT(" ",LEN(OFFSET(\$B\$4:\$D\$4,-(\$G2="M"),)))),
LEN(OFFSET(\$B\$4:\$D\$4,-(\$G2="M"),))),0))+2,0)&""```
Markmzz

10. ## Re: "it uses more levels of nesting than are allowed in the current file format"

Thanks for the quick reply. I have copied the table you added into excel and the formulas into L2 and M2. I have found it is not working yet, I have copied the table in below showing what it says in cells L2 and M2 after adding the formulas. L2 should say 'Obese Class I'. ????

 Table Gender Hight (cm) Weight (Kg) BMI WC (cm) BMI Catergory Overall Health Risk Waist Circumference M 163 80 30,1 93 Under Weight #N/A Body Mass Index (BMI) Men < 94cm Men 94 - 101,9cm Men ≥ 102cm F 163 80 30,1 93 #N/A #N/A Woman < 80cm Women 80 - 87,9cm Women ≥ 88cm Underweight <18,5 Increased Risk Healthy Weight 18,5 - 24,9 No Increased Risk No Increased Risk Increased Risk Overweight 25,0 - 29,9 No Increased Risk Increased Risk High Risk Obese Class I 30,0 - 34,9 Increased Risk High Risk Very High Risk Obese Class II 35,0 - 39,9 High Risk Very High Risk Extreme Risk Obese Class III ≥40 Very High Risk Extreme Risk Extreme Risk ********************* ********* ******************* ****************** ************** ** ******** ********* ********** **** ******** ************* *****************

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•