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

M

mattbird

Active Member
Joined
Oct 15, 2013
Messages
305
Office Version
  1. 2016
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"))))))))))))))
 
Sort by date Sort by votes
mattbird said:
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
*********************
*********
*******************
******************
**************
**
********
*********
**********
****
********
*************
*****************

<tbody>
</tbody>
Click to expand...

Try with this version of the table (with "." and not "," in the numbers):

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
*********************
*********
*******************
******************
**************
**
********
*********
**********
****
********
*************
*****************

<tbody>
</tbody>


Did you press Ctrl+Shift+Enter to enter the formulas?

By the way, what is your version of Excel (English, Portuguese or another)?


Markmzz
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Looking up data is easier done if the cells contain number rather than symbols like "<18.5"
If columns A and B are like this

Underweight 0
Healthy Weight 18.5
Overweight 25
Obese Class I 30
Obese Class II 35
Obese Class III 40

Then =LOOKUP(C1, B1:B6, A1:A6) will return the classification of a BMI entered in C1.
 
Upvote 0
Markmzz,

I have had another go at entering the formulas and realised I had forgot to press Ctrl+Shift+Enter. The L2 cell now says Obese Class III and the M2 cell says Very High Risk, which is wrong for the BMI 31.1. If I change the weight or waist circumference the table doesn't change the out come of L2 and M2. The same happen with Cell L3 and M2, but the Overall Health Risk does change if M or F is selected in cell G2 or G3. I have copied the table in below showing the results of the formulas:

TableGenderHight (cm)Weight (Kg)BMIWC (cm)BMI CatergoryOverall Health Risk
Waist CircumferenceM1638030,193Obese Class IIIVery High Risk
Body Mass Index (BMI)Men < 94cmMen 94 - 101,9cmMen ≥ 102cmF1638030,193Obese Class IIIExtreme Risk
Woman < 80cmWomen 80 - 87,9cmWomen ≥ 88cm
Underweight<18,5Increased Risk
Healthy Weight18,5 - 24,9No Increased RiskNo Increased RiskIncreased Risk
Overweight25,0 - 29,9No Increased RiskIncreased RiskHigh Risk
Obese Class I30,0 - 34,9Increased RiskHigh RiskVery High Risk
Obese Class II35,0 - 39,9High RiskVery High RiskExtreme Risk
Obese Class III≥40Very High RiskExtreme RiskExtreme Risk
********************************************************************************************************************************************************

<tbody>
</tbody>

Your continued help is greatly appreciated

Matt
 
Last edited:
Upvote 0
My Excel is in English. I did use "." between the numbers. I use a formula in cell J2 =(I2/(H2*H2)*10000) to work out the BMI automaticly.

Matt
 
Last edited:
Upvote 0
mattbird said:
My Excel is in English. I did use "." between the numbers. I use a formula in cell J2 =(I2/(H2*H2)*10000) to work out the BMI automaticly.

Matt
Click to expand...

I think that the formulas are working normal here. Look at this:

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
M
154
90
37,9
96
Obese Class II
Very High Risk
Underweight
<18,5
Increased Risk
F
163
70
26,3
90
Overweight
High Risk
Healthy Weight
18,5 - 24,9
No Increased Risk
No Increased Risk
Increased Risk
M
163
65
24,5
79
Healthy Weight
No Increased Risk
Overweight
25,0 - 29,9
No Increased Risk
Increased Risk
High Risk
F
163
65
24,5
87
Healthy Weight
No Increased Risk
Obese Class I
30,0 - 34,9
Increased Risk
High Risk
Very High Risk
M
154
90
37,9
100
Obese Class II
Very High Risk
Obese Class II
35,0 - 39,9
High Risk
Very High Risk
Extreme Risk
F
167
50
17,9
75
Underweight
Increased Risk
Obese Class III
≥40
Very High Risk
Extreme Risk
Extreme Risk
M
163
110
41,4
102
Obese Class III
Extreme Risk
*********************
*********
*******************
******************
**************
**
********
*********
**********
****
********
*************
*****************

<tbody>
</tbody>


PS: here (in my Windows/Excel configuration) . is ,


Or I'm wrong?

Markmzz
 
Upvote 0
I have tried the second table and it seams to be working, awesome. What do the stars mean at the bottom of the table? are they just for the number of characters in each box? Can I delete that row?

If I have any further questions can I contact you please?

Thanks for your help

Matt
 
Upvote 0
mattbird said:
I have tried the second table and it seams to be working, awesome. What do the stars mean at the bottom of the table? are they just for the number of characters in each box? Can I delete that row?

If I have any further questions can I contact you please?

Thanks for your help

Matt
Click to expand...

You're welcome.

The stars are used to facilitate the display of data on the worksheet here in the forum. You can delete that row (no problem).

No problem too if your have more questions.

Markmzz
 
Upvote 0
Hello Could anyone help as I've reached the nesting levels and cannot think how to overcome it, =IF((H17)<$N$3,0,IF(AND((H17)>$N$2,(H17)<$N$4),1,IF(AND((H17)>$N$3,(H17)<$N$5),2,IF(AND((H17)>$N$4,(H17)<$N$6),3,IF(AND((H17)>7,(H17)<$N$7),4,IF(AND((H17)>$N$6,(H17)<$N$8),5,IF(AND((H17)>10,(H17)<100),6,IF(AND((H17)>12,(H17)<$R$5,7)))))))) many thanks
 
Upvote 0
MarkDargan said:
Hello Could anyone help as I've reached the nesting levels and cannot think how to overcome it, =IF((H17)<$N$3,0,IF(AND((H17)>$N$2,(H17)<$N$4),1,IF(AND((H17)>$N$3,(H17)<$N$5),2,IF(AND((H17)>$N$4,(H17)<$N$6),3,IF(AND((H17)>7,(H17)<$N$7),4,IF(AND((H17)>$N$6,(H17)<$N$8),5,IF(AND((H17)>10,(H17)<100),6,IF(AND((H17)>12,(H17)<$R$5,7)))))))) many thanks
Click to expand...

Hi MarkDargan,

I think that the best way is to create a new thread.

And, in the new thread, post what you have and what you want (a small example with data and formula).

Markmzz
 
Upvote 0
Markmzz,

If I was to add two new columns to the table Cell 'N' (assessment date) and 'O' (Review Date), and imputed a date of the assessment in Cell N2 (assessment date), how could I get Cell O2 (Review Date) to work out the date of next review depending on cell M2 (Overall Health Risk):

No Increase Risk = annually review
Increased Risk = 6 monthly review
High Risk = 3 monthly review
Very High Risk = monthly review
Extreme Risk = monthly review

And cell O2 (review date) to show green and then orange 1 week to review and Red if passed date and green again when new date entered, if you understand what I mean.

This would mean I could monitor each person I see and help them keep on top of there weight.

Your help as always is greatly appreciated

Matt
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Risk Assessment Calculator
Replies
1
Views
554
JamesCanale
JamesCanale
A
  • Question
Create Duplicate records based on possible combinations of other values
Replies
7
Views
551
kvsrinivasamurthy
K
M
  • Question
unique SKU
Replies
1
Views
337
Sam_D_Ben
S
G
  • Question
Complex problem - how many widgets should I sell?
Replies
9
Views
440
Dan_W
D
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
891
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,215,326
Messages
6,124,256
Members
449,149
Latest member
mwdbActuary

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top