"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:
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
Click to expand...

Try this:

Layout

Gender
Hight (cm)
Weight (Kg)
BMI
WC (cm)
BMI Catergory
Overall Health Risk
Assessment Date
Review Date
Overall Health Risk
Months
M
163
80
30,1
93
Obese Class I
Increased Risk
01/01/2013
01/07/2013
No Increased Risk
12
F
163
80
30,1
93
Obese Class I
Very High Risk
01/02/2013
01/03/2013
Increased Risk
6
M
154
90
37,9
96
Obese Class II
Very High Risk
01/03/2013
01/04/2013
High Risk
3
F
163
70
26,3
90
Overweight
High Risk
01/04/2013
01/07/2013
Very High Risk
1
M
163
65
24,5
79
Healthy Weight
No Increased Risk
01/05/2013
01/05/2014
Extreme Risk
1
F
163
65
24,5
87
Healthy Weight
No Increased Risk
01/06/2013
01/06/2014
M
154
90
37,9
100
Obese Class II
Very High Risk
01/07/2013
01/08/2013
F
167
50
17,9
75
Underweight
Increased Risk
01/08/2013
01/02/2014
M
163
110
41,4
102
Obese Class III
Extreme Risk
01/09/2013
01/10/2013
********
*********
**********
****
********
*************
*****************
*****************
*****************
**
*******************
*******

<tbody>
</tbody>


Formula

Code: 
In O2

=EDATE($N2,VLOOKUP($M2,$Q$2:$R$6,2,0))

Look at the table in the range $Q$2:$R$6 (in blue).

Markmzz
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
nearly right,

GenderHight (cm)Weight (Kg)BMIWC (cm)BMI CatergoryOverall Health RiskAssessment DateReview Date
M1638030,193Obese Class IIncreased Risk01/01/201301/07/2013
F1638030,193Obese Class IVery High Risk01/02/201301/03/2013
M1549037,996Obese Class IIVery High Risk01/03/201301/04/2013
F1637026,390OverweightHigh Risk01/04/201301/07/2013
M1636524,579Healthy WeightNo Increased Risk01/05/201301/05/2014
F1636524,587Healthy WeightNo Increased Risk01/06/201301/06/2014
M1549037,9100Obese Class IIVery High Risk01/07/201301/08/2013
F1675017,975UnderweightIncreased Risk01/08/201301/02/2014
M16311041,4102Obese Class IIIExtreme Risk01/09/201301/10/2013
***********************************************************************************************************************************

<tbody>
</tbody>

Dont need cell Q and R just N and O. ie

(cell N) assessment date 1/1/13 (cell O) Review date would show 1/7/13 because the (cell M) overall health risk shows increased risk.

No Increased risk category's need to be seen every 12 months
Increases risk category's need to be seen every 6 months
High risk category's needs to be seen every 3 months
Very high risk and Extreme risk category's need to be seen every month


Cell O also needs to change colour, starting on Green then 1 week before review date to turn orange and to turn Red if review date runs over and to turn green again when new review date is inserted.
 
Upvote 0
mattbird said:
nearly right,

Dont need cell Q and R just N and O. ie

(cell N) assessment date 1/1/13 (cell O) Review date would show 1/7/13 because the (cell M) overall health risk shows increased risk.

No Increased risk category's need to be seen every 12 months
Increases risk category's need to be seen every 6 months
High risk category's needs to be seen every 3 months
Very high risk and Extreme risk category's need to be seen every month


Cell O also needs to change colour, starting on Green then 1 week before review date to turn orange and to turn Red if review date runs over and to turn green again when new review date is inserted.
Click to expand...

Maybe this:

Layout

BMI CatergoryOverall Health RiskAssessment DateReview Date
Obese Class IIncreased Risk27/04/201327/10/2013
Obese Class IVery High Risk28/09/201328/10/2013
Obese Class IIVery High Risk01/08/201301/09/2013
OverweightHigh Risk01/08/201301/11/2013
Healthy WeightNo Increased Risk01/05/201301/05/2014
Healthy WeightNo Increased Risk01/06/201301/06/2014
Obese Class IIVery High Risk01/07/201301/08/2013
UnderweightIncreased Risk01/08/201301/02/2014
Obese Class IIIExtreme Risk01/09/201301/10/2013
****************************************************************

<colgroup><col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3437;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;" span="2"> <tbody>
</tbody>


Formula

Code: 
In O2

=EDATE($N2,IF($M2="No Increased Risk",12,IF($M2="Increased Risk",6,IF($M2="High Risk",3,1))))

Conditional Formatting

Code: 
Red: =$O2 < TODAY()

Green: =$O2 > TODAY()+7

Orange: =AND($O2 >= TODAY(),$O2 <= TODAY()+7)

More information about CF:

http://www.contextures.com/xlCondFormat03.html


Markmzz
 
Upvote 0
Markmzz,

Do you know how to enter code in the work sheet view code option? I am trying to get excel to copy the formulas from my BMI table to the next row once I have filled it out.
example: if I enter data in row 17 how can I get it to automatic set up row 18 and so on. Otherwise I have to copy the formulas from row 17 and past them into row 18. do you know how to make this work?

Your help is greatly appreciated

Mat.
 
Upvote 0
mattbird said:
Markmzz,

Do you know how to enter code in the work sheet view code option? I am trying to get excel to copy the formulas from my BMI table to the next row once I have filled it out.
example: if I enter data in row 17 how can I get it to automatic set up row 18 and so on. Otherwise I have to copy the formulas from row 17 and past them into row 18. do you know how to make this work?

Your help is greatly appreciated

Mat.
Click to expand...

Sorry, but I didn't understand what you want.

Could you post a example (with data)?

Markmzz
 
Upvote 0
Markmzz,

the conditioning formatting you showed me is working well (column M) if column 'L' has no data entered column 'M' (Review Date) shows red how do I get it to show white instead when there is not data entered in column 'L'?
 
Upvote 0
mattbird said:
Markmzz,

the conditioning formatting you showed me is working well (column M) if column 'L' has no data entered column 'M' (Review Date) shows red how do I get it to show white instead when there is not data entered in column 'L'?
Click to expand...

Try this (look at the rows 11 and 12, that rows have formulas and conditional formatting):

Layout

Table
Gender
Hight (cm)
Weight (Kg)
BMI
WC (cm)
BMI Catergory
Overall Health Risk
Assessment Date
Review Date
Waist Circumference
M
163
80
30,1
93
Obese Class I
Increased Risk
27/04/2013
27/10/2013
Body Mass Index (BMI)
Men < 94cm
Men 94 - 101,9cm
Men ≥ 102cm
F
163
80
30,1
93
Obese Class I
Very High Risk
28/09/2013
28/10/2013
Woman < 80cm
Women 80 - 87,9cm
Women ≥ 88cm
M
154
90
37,9
96
Obese Class II
Very High Risk
21/10/2013
21/11/2013
Underweight
<18,5
Increased Risk
F
163
70
26,3
90
Overweight
High Risk
19/08/2013
19/11/2013
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
01/05/2013
01/05/2014
Overweight
25,0 - 29,9
No Increased Risk
Increased Risk
High Risk
F
163
65
24,5
87
Healthy Weight
No Increased Risk
01/06/2013
01/06/2014
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
01/07/2013
01/08/2013
Obese Class II
35,0 - 39,9
High Risk
Very High Risk
Extreme Risk
F
167
50
17,9
75
Underweight
Increased Risk
01/08/2013
01/02/2014
Obese Class III
≥40
Very High Risk
Extreme Risk
Extreme Risk
M
163
110
41,4
102
Obese Class III
Extreme Risk
01/09/2013
01/10/2013
**
**
********
*********
*******************
******************
**************
**
********
*********
**********
****
********
*************
*****************
*****************
*****************

<tbody>
</tbody>



Formulas

Code: 
In L2 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF($J2="","",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 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF($L2="","",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)&"")

In O2

=IF($N2="","",EDATE($N2,IF(M2="No Increased Risk",12,IF(M2="Increased Risk",6,IF(M2="High Risk",3,1)))))


Conditional Formatting

Code: 
Red: =AND($O2 < TODAY(),$O2 <> "")

Green: =AND($O2 > TODAY()+7,$O2 <> "")

Orange: =AND($O2 >= TODAY(),$O2 <= TODAY()+7)

Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,215,035
Messages
6,122,791
Members
449,095
Latest member
m_smith_solihull

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