Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

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

  1. #11
    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 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
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,629
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Yes.

  3. #13
    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

    Thank you very much indeed

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

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

    You're welcome.

  5. #15
    New Member
    Join Date
    May 2017
    Posts
    2
    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 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
    6,422
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    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
    5,253
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    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
    6,422
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    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

  9. #19
    New Member
    Join Date
    May 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Eric,

    That is much easier! I only have one issue left to solve....

    For some reason it will only match certain labels... Such as this picture... I have 48BC in the product type but it gives me 32C price at 3,000... Any ideas?

    Here is the formula... =VLOOKUP(D11,$B$3:$E$7,MATCH(C11,$C$2:$E$2)+1)


    B C D E
    2 48BC 32C NTC
    3 0 1 6 11
    4 3000 2 7 12
    5 10000 3 8 13
    6 25000 4 9 14
    7 50000 5 10 15
    Product QTY. Value
    48BC 3000 7

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