Yes.
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 ...
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
Yes.
Thank you very much indeed
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!!!
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
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
- 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
Like this thread? Share it with others