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

 

[mattbird]

The code for cell M2 says uses more levels of nesting than are allowed and highlights 'SUBSTITUTE(OFFSET($B$4:$D$4,-($G2="M"),),"cm","")),1)="9",0.1,0)+?

 

[markmzz]

Hi Mattbird,

Could your post the version of your Excel and all the formula (not only a part) that you used?

Markmzz

 

[markmzz]

Another way:

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

**

**

*********************

*********

*******************

******************

**************

**

*******

*********

**********

****

*******

*************

*****************

***************

************

<colgroup><col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="56" style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;"> <col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;"> <col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3693;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="54" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;"> <col width="60" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;"> <col width="28" style="width: 21pt; mso-width-source: userset; mso-width-alt: 1024;"> <col width="45" style="width: 34pt; mso-width-source: userset; mso-width-alt: 1645;"> <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="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <tbody>
</tbody>

Names

BMI - Refers to: ={0;18.5;25;30;35;40}

WCM - Refers to: ={0,94,102}

WCW - Refers to: ={0,80,88}

Formulas

In L2

=IF($J2="","",LOOKUP($J2,BMI,$A$5:$A$10))

In M2

=IF($L2="","",VLOOKUP($L2,$A$5:$E$10,MATCH($K2,IF($G2="M",WCM,WCW))+COLUMNS($A$3:$B$3),0))

In O2

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

Markmzz

 

[markmzz]

A small modification in my last formulas:

If you don't want to use names, try this:

In L2

=IF($J2="","",LOOKUP($J2,{0;18.5;25;30;35;40},$A$5:$A$10))

In M2

=IF($L2="","",VLOOKUP($L2,$A$5:$E$10,MATCH($K2,IF($G2="M",{0,94,102},{0,80,88}))+COLUMNS($A$3:$B$3),0))

Markmzz

 

[mattbird]

Markmzz,

Sorry for not getting back to you been busy. I have sorted the last issue thanks fro your help. I have three questions I am hoping you may be able to help me with? I have added to control buttons, the first button opens another worksheet in the workbook:

Private Sub CommandButton2_Click()
Application.Goto Worksheets("Stats 1").Range("B6")

End Sub

but when I hide the sheet I want it to open It shows the following error:

Run-time error '1004':
Method 'Goto'of object'_Application'failed

How do I get this to work?

The other button sends data to the same worksheet and puts it into another table which then shows the results in a graph:

Private Sub CommandButton1_Click()
Dim CustomerDate As Date, CustomerWeight As Integer, CustomerBMI As Integer, CustomerWC As Integer
Worksheets("Department1").Select
CustomerDate = Range("L17")
CustomerWeight = Range("G17")
CustomerBMI = Range("H17")
CustomerWC = Range("I17")
Worksheets("Stats 1").Select
Worksheets("Stats 1").Range("B5").Select
If Worksheets("Stats 1").Range("B5").Offset(1, 0) <> "" Then
Worksheets("Stats 1").Range("B5").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CustomerDate
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = CustomerWeight
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = CustomerBMI
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = CustomerWC
Worksheets("Department1").Select
Worksheets("Department1").Range("G18").Select


End Sub

but when I hide the sheet I want it to open It shows the following error:

Run-time error '1004':
Select method of Worksheet class failed

How do I get around this?

My final question is, I would like to open the same sheet from another workbooks control button, but don't know where to start with the code.
Can you please help???

 

[markmzz]

Hi Mattbird,

First, I'm glad that you first problem was solved.

Second, I'm sorry, but I don't have time now to work with code.

My suggestion is that you start a new topic with all information about you problem (what you have and what you want).

Markmzz

 

[mattbird]

Markmzz,

I would like to say first of all thanks for your help with my table it is working as I wanted so thank you.

There is however a problem I came across that I was not aware of and this is that something on Excell 2007 are not compatible with Excell 2003, so some of the codes you wrote for me do not work; I get the following Security Message:'Macros my contain viruses. It is usually safe to disable macros, but if the macros are legitimate, you might loss some functions'. I then click the 'Enable Macros' The workbook opens but my formulas (LOOKUP, VLOOKUP and EDATE) show '#NAME'.

{=LOOKUP($N15,_xlfn.IFERROR(IF(RIGHT($B$6:$B$11)="9",0.1,0)+RIGHT($B$6:$B$11,4),0),$A$7:$A$12)}


{=VLOOKUP($P15,$A$7:$E$12,MATCH($O15,_xlfn.IFERROR(IF(RIGHT(TRIM(SUBSTITUTE(O FFSET($B$6:$D$6,-($K15="M"),),"cm","")),1)="9",0.1,0)+RIGHT(SUBSTITUTE(SUBSTITUTE(OFFSET($B$6:$D$6,-($K15="M"),),"cm","")," ",REPT(" ",LEN(OFFSET($B$6:$D$6,-($K15="M"),)))),LEN(OFFSET($B$6:$D$6,-($K15="M"),))),0))+2,0)&""}


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

Are you able to help me with these codes so they work in Excell 2003 please? as I am stuck.

Kind Regards

Matt

 

[markmzz]

Markmzz,

I would like to say first of all thanks for your help with my table it is working as I wanted so thank you.

There is however a problem I came across that I was not aware of and this is that something on Excell 2007 are not compatible with Excell 2003, so some of the codes you wrote for me do not work; I get the following Security Message:'Macros my contain viruses. It is usually safe to disable macros, but if the macros are legitimate, you might loss some functions'. I then click the 'Enable Macros' The workbook opens but my formulas (LOOKUP, VLOOKUP and EDATE) show '#NAME'.

{=LOOKUP($N15,_xlfn.IFERROR(IF(RIGHT($B$6:$B$11)="9",0.1,0)+RIGHT($B$6:$B$11,4),0),$A$7:$A$12)}


{=VLOOKUP($P15,$A$7:$E$12,MATCH($O15,_xlfn.IFERROR(IF(RIGHT(TRIM(SUBSTITUTE(O FFSET($B$6:$D$6,-($K15="M"),),"cm","")),1)="9",0.1,0)+RIGHT(SUBSTITUTE(SUBSTITUTE(OFFSET($B$6:$D$6,-($K15="M"),),"cm","")," ",REPT(" ",LEN(OFFSET($B$6:$D$6,-($K15="M"),)))),LEN(OFFSET($B$6:$D$6,-($K15="M"),))),0))+2,0)&""}


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

Are you able to help me with these codes so they work in Excell 2003 please? as I am stuck.

Kind Regards

Matt

Try this (with the layout of post #33):

New Formulas

In L2

=IF($J2="","",LOOKUP($J2,{0;18.5;25;30;35;40},$A$5:$A$10))

In M2

=IF($L2="","",VLOOKUP($L2,$A$5:$E$10,MATCH($K2,IF($G2="M",{0,94,102},{0,80,88}))+COLUMNS($A$3:$B$3),0))

In O2

=IF($N2="","",DATE(YEAR($N2),MONTH($N2)+IF($M2="No Increased Risk",12,IF($M2="Increased Risk",6,IF($M2="High Risk",3,1))),DAY($N2)))

Markmzz

 

[mattbird]

Markmzz,

Thanks for your reply. I will try it tomorrow on Excel 2003 and let you know. It works on my Excel 2007.

Matt

 

[mattbird]

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