Thanks Thanks:  0
Likes Likes:  0
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 44

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

  1. #1
    Board Regular
    Join Date
    Oct 2013
    Posts
    268
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question "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?

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

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,259
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    10 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Oct 2013
    Location
    Belo Horizonte, Brazil
    Posts
    695
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default 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. #4
    Board Regular
    Join Date
    Oct 2013
    Posts
    268
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    My spread sheet layout

    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.

    Your help is greatly appreciated

    Matt
    Last edited by mattbird; Oct 15th, 2013 at 06:17 PM.

  5. #5
    Board Regular
    Join Date
    Oct 2013
    Posts
    268
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

    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.

    Your help is greatly appreciated

    Matt
    Last edited by mattbird; Oct 15th, 2013 at 06:47 PM.

  6. #6
    Board Regular
    Join Date
    Oct 2013
    Posts
    268
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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


    My spread sheet layout

    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.

    Your help is greatly appreciated

    Matt
    Last edited by mattbird; Oct 15th, 2013 at 07:16 PM.

  7. #7
    Board Regular
    Join Date
    Oct 2013
    Location
    Belo Horizonte, Brazil
    Posts
    695
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default 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.

    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.

  8. #8
    Board Regular
    Join Date
    Oct 2013
    Posts
    268
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

    Thanks for your help.

  9. #9
    MrExcel MVP
    Join Date
    May 2011
    Posts
    3,699
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default 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. #10
    Board Regular
    Join Date
    Oct 2013
    Posts
    268
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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