Thanks:  0
Likes:  0

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

1. ## Re: how i can use more than 64 levels of nesting

Originally Posted by shg
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?

Yes.

3. ## Re: how i can use more than 64 levels of nesting

Thank you very much indeed

4. ## Re: how i can use more than 64 levels of nesting

You're welcome.

5. ## Re: how i can use more than 64 levels of nesting

Originally Posted by shg
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. ## 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

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

8. ## Re: how i can use more than 64 levels of nesting

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

9. ## 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

#### Posting Permissions

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