Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18
Like Tree1Likes

how i can use more than 64 levels of nesting

This is a discussion on how i can use more than 64 levels of nesting within the Excel Questions forums, part of the Question Forums category; Originally Posted by shg Just make a table with all of the descriptions in one column and the relevant measure ...

  1. #11
    New Member
    Join Date
    Mar 2016
    Posts
    8

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

    Quote Originally Posted by shg View Post
    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
    Hello shg and thank you for the quick reply. I am actually going to use this when I will make the data entries for the purchasing invoices, so I want it to calculate each time the correct amount of kilos/litres/pieces that I purchased each time when I am typing the different quantities. Will this work like with the lookup formula that you have suggested?

    Thanks in advance

  2. #12
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,112

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

    Yes.
    RoryA likes this.

  3. #13
    New Member
    Join Date
    Mar 2016
    Posts
    8

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

    Thank you very much indeed

  4. #14
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,112

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

    You're welcome.

  5. #15
    New Member
    Join Date
    May 2017
    Posts
    1

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

    Quote Originally Posted by shg View Post
    You're welcome.

    I have an issue with nesting 64 functions as well...

    Here is my Problem...

    Lets say I have three items EX: Coke, Sprite, Dr. Pepper.
    Next, lets say that if I buy 10 Bottles of Coke it cost $25, if i buy 20 Bottles of Coke it cost $23, if I buy 10 bottles of Sprite it Cost $27, etc....

    I have this formula as an =IF(AND(R5="Coke",Q5>=50),"$23.50",IF(AND(R5="Coke",Q5>=40),"$24.50", Etc.

    So in the cell it would show that the price I would pay for 50 or more cokes would be $23.50.


    Is there a better way to do this...

    The information I am trying to enter has 24 different types (Such as Coke, Sprite, and Dr. Pepper), and each type has 6 possible prices...

    I use this when calculating cost in my business. This saves lots of time currently but I can only input ten different types currently...

    I am a box guru not an excel guru...


    Thank you for any help!!!

  6. #16
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    5,440

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

    Have not read the rest if this thread, but nesting that deep is just asking for problems. Working with that many variables can almost always be simplified by using a table and/or helper columns. So, from the sounds of it, you could have a table with types down the side, and process across the top - then use INDEX/MATCH

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  7. #17
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    4,551

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

    To expand a bit on Ford's comment, here's one way to do it:

    A B C D E Q R S T
    1 Cutoff Coke Sprite Dr. Pepper
    2 0 $ 30.00 $ 31.00 $ 29.00
    3 10 $ 25.00 $ 27.00 $ 28.00
    4 40 $ 24.50 $ 25.75 $ 26.00 Product Quantity Cost
    5 50 $ 23.50 $ 24.00 $ 23.00 Sprite 23 $ 27.00
    6 75 $ 22.00 $ 23.00 $ 22.50
    7 100 $ 20.00 $ 21.00 $ 21.50
    Sheet20

    Worksheet Formulas
    Cell Formula
    T5 =VLOOKUP(S5,$A$2:$D$7,MATCH(R5,$B$1:$D$1)+1)



    You just build your table as shown. You can put it on another sheet if you want. This assumes that the cutoff points are the same for all products. If Sprite drops to $27 at a quantity of 20 instead of 10 like Coke, then we'd need to come up with something a bit fancier. The MATCH figures out which column to look at, and the VLOOKUP finds the right value from column A, and grabs the total from the appropriate column. Far easier to change than a 64-level IF formula.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  8. #18
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    5,440

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

    Quote Originally Posted by Eric W View Post
    To expand a bit on Ford's comment, here's one way to do it:...........
    nice example

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

Page 2 of 2 FirstFirst 12

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