how i can use more than 64 levels of nesting

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: how i can use more than 64 levels of nesting

  1. #1
    New Member
    Join Date
    Dec 2013
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation how i can use more than 64 levels of nesting

     
    =if(and(p1=1,b37=hpe!c5),hpe!c6,if(and(p1=1,b37=hpe!d5),hpe!d6,if(and(p1=1,b37=hpe!e5),cosa!e6,if(and(p1=1,b37=hpe!f5),h pe!f6,if(and(p1=1,b37=hpe!g5),hpe!g6,if(and(p1=2,b37=hpe!c5),hpe!c7,if(and(p1=2,b37=hpe!d5),hpe!d7,if(and(p1=2,b37=hpe!e 5),cosa!e7,if(and(p1=2,b37=hpe!f5),hpe!f7,if(and(p1=2,b37=hpe!g5),hpe!g7,if(and(p1=3,b37=hpe!c5),hpe!c8,if(and(p1=3,b37= hpe!d5),hpe!d8,if(and(p1=3,b37=hpe!e5),cosa!e8,if(and(p1=3,b37=hpe!f5),hpe!f8,if(and(p1=3,b37=hpe!g5),hpe!g8,if(and(p1=4 ,b37=hpe!c5),hpe!c9,if(and(p1=4,b37=hpe!d5),hpe!d9,if(and(p1=4,b37=hpe!e5),cosa!e9,if(and(p1=4,b37=hpe!f5),hpe!f9,if(and (p1=4,b37=hpe!g5),hpe!g9,if(and(p1=5,b37=hpe!c5),hpe!c10,if(and(p1=5,b37=hpe!d5),hpe!d10,if(and(p1=5,b37=hpe!e5),cosa!e1 0,if(and(p1=5,b37=hpe!f5),hpe!f10,if(and(p1=5,b37=hpe!g5),hpe!g10,if(and(p1=6,b37=hpe!c5),hpe!c11,if(and(p1=6,b37=hpe!d5 ),hpe!d11,if(and(p1=6,b37=hpe!e5),cosa!e11,if(and(p1=6,b37=hpe!f5),hpe!f11,if(and(p1=6,b37=hpe!g5),hpe!g11,if(and(p1=7,b 37=hpe!c5),hpe!c12,if(and(p1=7,b37=hpe!d5),hpe!d12,if(and(p1=7,b37=hpe!e5),cosa!e12,if(and(p1=7,b37=hpe!f5),hpe!f12,if(a nd(p1=7,b37=hpe!g5),hpe!g12,if(and(p1=8,b37=hpe!c5),hpe!c13,if(and(p1=8,b37=hpe!d5),hpe!d13,if(and(p1=8,b37=hpe!e5),cosa !e13,if(and(p1=8,b37=hpe!f5),hpe!f13,if(and(p1=8,b37=hpe!g5),hpe!g13,if(and(p1=9,b37=hpe!c5),hpe!c14,if(and(p1=9,b37=hpe !d5),hpe!d14,if(and(p1=9,b37=hpe!e5),cosa!e14,if(and(p1=9,b37=hpe!f5),hpe!f14,if(and(p1=9,b37=hpe!g5),hpe!g14,if(and(p1= 10,b37=hpe!c5),hpe!c15,if(and(p1=10,b37=hpe!d5),hpe!d15,if(and(p1=10,b37=hpe!e5),cosa!e15,if(and(p1=10,b37=hpe!f5),hpe!f 15,if(and(p1=10,b37=hpe!g5),hpe!g15,if(and(p1=11,b37=hpe!c5),hpe!c16,if(and(p1=11,b37=hpe!d5),hpe!d16,if(and(p1=11,b37=h pe!e5),cosa!e16,if(and(p1=11,b37=hpe!f5),hpe!f16,if(and(p1=11,b37=hpe!g5),hpe!g16,if(and(p1=12,b37=hpe!c5),hpe!c17,if(an d(p1=12,b37=hpe!d5),hpe!d17,if(and(p1=12,b37=hpe!e5),cosa!e17,if(and(p1=12,b37=hpe!f5),hpe!f17,if(and(p1=12,b37=hpe!g5), hpe!g17,if(and(p1=13,b37=hpe!c5),hpe!c18,if(and(p1=13,b37=hpe!d5),hpe!d18,if(and(p1=13,b37=hpe!e5),cosa!e18,if(and(p1=13 ,b37=hpe!f5),hpe!f18,-------------," "))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

  2. #2
    Board Regular dispelthemyth's Avatar
    Join Date
    Mar 2006
    Location
    England
    Posts
    632
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how i can use more than 64 levels of nesting

    I would immediately stop using the IF statement and rephrase your question so potentially a VBA or alternative formula can be devised
    Financial Modelling Consultant
    Excel 2016 32bit and 2016 64bit + Windows 10

    I've always listening so i'm always learning
    Before posting your question, reread it to make sure you understand it.
    If possible post example data to make it easier to help you

  3. #3
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,484
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how i can use more than 64 levels of nesting

    Going out on a limb and never tried it like this but try using name ranges.

  4. #4
    Board Regular
    Join Date
    Sep 2009
    Posts
    470
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how i can use more than 64 levels of nesting

    This looks extremely inefficient. Looking at the formula you have entered so far:

    you are looking to see if B37 appears in HPE!C5:G5
    When P1 = 1 you want to return 1 row below, P2 = 2 rows below, P3=3 rows below etc

    In which case you can use:

    =INDEX(HPE!C:G,P1+1,MATCH(B37,HPE!C$5:G$5))


    If that doesn't work a you want, as the above poster says, please rephrase your question: tell us the layout of your data and what you want to achieve.



  5. #5
    Board Regular
    Join Date
    Sep 2009
    Posts
    470
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how i can use more than 64 levels of nesting

    Revised formula:


    =INDEX(HPE!C:G,P1+5,MATCH(B37,HPE!C5:G5,0))

    Let us know how you get on. There is no need for such a complex if statement, when you are only testing 2 conditions (roll number and Category (sport, 1st aid, gardening, etc)

  6. #6
    Board Regular
    Join Date
    Jul 2009
    Location
    Woking UK
    Posts
    2,748
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how i can use more than 64 levels of nesting

    Hi

    Perhaps :-
    Code:
    =IF(MATCH(B37,HPE!$C$5:$G$5,0)<>3,INDEX(HPE!C:G,P1+5,MATCH(B37,HPE!$C$5:$G$5,0)),INDEX(Cosa!$E:$E,P1+5,1))
    Or if the ranges are as per your original "Closed" thread then as per CROY1985 post #5.

    hth
    Last edited by ukmikeb; Dec 31st, 2013 at 07:53 AM.
    Mike

    -----------------------------------------------
    Some solutions don't require an IF!

    When posting code wrap your code between [CODE] tags, eg. [ code ] your code [ /code] - no spaces .

    Paste your Excel data...

    MrExcel HTML Maker or Excel Jeanie

  7. #7
    New Member
    Join Date
    Dec 2013
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Re: how i can use more than 64 levels of nesting

    Quote Originally Posted by CROY1985 View Post
    Revised formula:=INDEX(HPE!C:G,P1+5,MATCH(B37,HPE!C5:G5,0))Let us know how you get on. There is no need for such a complex if statement, when you are only testing 2 conditions (roll number and Category (sport, 1st aid, gardening, etc)
    Thank you very much sir. Your revised formula runs successfully

  8. #8
    New Member
    Join Date
    Mar 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how i can use more than 64 levels of nesting

    Quote Originally Posted by ukmikeb View Post
    Hi

    Perhaps :-
    Code:
    =IF(MATCH(B37,HPE!$C$5:$G$5,0)<>3,INDEX(HPE!C:G,P1+5,MATCH(B37,HPE!$C$5:$G$5,0)),INDEX(Cosa!$E:$E,P1+5,1))
    Or if the ranges are as per your original "Closed" thread then as per CROY1985 post #5.

    hth
    Hello, I would like to make a function that generates a result depending on type of measurement but I am limited by the 64 limitation of nested ifs. The H2 of my excel includes types of measurements : Box of 5 kilos so I have to multiply the I2 cell which has the quantity by 5 to get kilos, box of 30 kilos*30 and so on. I have many unit types (112) so how could I make this??? I really need this for my work. I am including a photo to see how I do it (I put the word function so u can see the function that I am using). I tries concatenate but it generates multiple values and I only need one.
    EXAMPLE_zpsqrhvgkpy.jpg Photo by Konstantinos_Psilakis | Photobucket
    Sorry in advance for my inexperience

  9. #9
    New Member
    Join Date
    Mar 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how i can use more than 64 levels of nesting

    Quote Originally Posted by ukmikeb View Post
    Hi

    Perhaps :-
    Code:
    =IF(MATCH(B37,HPE!$C$5:$G$5,0)<>3,INDEX(HPE!C:G,P1+5,MATCH(B37,HPE!$C$5:$G$5,0)),INDEX(Cosa!$E:$E,P1+5,1))
    Or if the ranges are as per your original "Closed" thread then as per CROY1985 post #5.

    hth
    Hello, I would like to make a function that generates a result depending on type of measurement but I am limited by the 64 limitation of nested ifs. The H2 of my excel includes types of measurements : Box of 5 kilos so I have to multiply the I2 cell which has the quantity by 5 to get kilos, box of 30 kilos*30 and so on. I have many unit types (112) so how could I make this??? I really need this for my work. I am including a photo to see how I do it (I put the word function so u can see the function that I am using). I tries concatenate but it generates multiple values and I only need one.
    EXAMPLE_zpsqrhvgkpy.jpg Photo by Konstantinos_Psilakis | Photobucket
    Sorry in advance for my inexperience

  10. #10
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,735
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how i can use more than 64 levels of nesting

      
    Just make a table with all of the descriptions in one column and the relevant measure in the next, and use a lookup formula.

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

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
  •  

 

 
DMCA.com