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

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
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
 
Upvote 0
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.
 
Upvote 0
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

<tbody>
</tbody>

<tbody>
</tbody>

My spread sheet layout

GenderHight (cm)Weight (Kg)BMIWC (cm)BMI CatergoryOverall Health Risk
M1638030.193Obese Class IIncreased Risk

<tbody>
</tbody>


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.

Your help is greatly appreciated

Matt
 
Last edited:
Upvote 0
Please disregard my first reply table didn't come out correctly.

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


My spread sheet layout

GenderHight (cm)Weight (Kg)BMIWC (cm)BMI CatergoryOverall Health Risk
M1638030.193Obese Class IIncreased Risk

<tbody>
</tbody>


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.

Your help is greatly appreciated

Matt
 
Last edited:
Upvote 0
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 < 94cmMen 94 - 101.9cmMen ≥ 102cm
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>


My spread sheet layout

GenderHight (cm)Weight (Kg)BMIWC (cm)BMI CatergoryOverall Health Risk
M1638030.193Obese Class IIncreased Risk

<tbody>
</tbody>



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.

Your help is greatly appreciated

Matt
 
Last edited:
Upvote 0
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.

As you have a spreadsheet with a lot of things there,to easier it, please, save your spreadsheet example with no sensitive data but with enough data to understand well what you desire at, Upload - Speedy Share - upload your files here, putting link here for download.

I believe we can help you to build a trustable spreadsheet.
 
Upvote 0
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.

Thanks for your help.
 
Upvote 0
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
*********************
*********
*******************
******************
**************
**
********
*********
**********
****
********
*************
*****************

<tbody>
</tbody>


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
 
Upvote 0
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'. ????

TableGenderHight (cm)Weight (Kg)BMIWC (cm)BMI CatergoryOverall Health Risk
Waist CircumferenceM1638030,193Under Weight#N/A
Body Mass Index (BMI)Men < 94cmMen 94 - 101,9cmMen ≥ 102cmF1638030,193#N/A#N/A
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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Risk Assessment Calculator
Replies
1
Views
533
JamesCanale
JamesCanale
A
  • Question
Create Duplicate records based on possible combinations of other values
Replies
7
Views
535
kvsrinivasamurthy
K
M
  • Question
unique SKU
Replies
1
Views
327
Sam_D_Ben
S
G
  • Question
Complex problem - how many widgets should I sell?
Replies
9
Views
428
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
862
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,214,391
Messages
6,119,249
Members
448,879
Latest member
oksanana

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