how i can use more than 64 levels of nesting

[nskatoch]

=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),hpe!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!e5),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!e10,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,b37=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(and(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!f15,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=hpe!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(and(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,-------------," "))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

 

[syban]

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

 

[shg]

Yes.

 

[syban]

Thank you very much indeed

 

[shg]

You're welcome.

 

[boxguru]

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!!!

 

[FDibbins]

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

 

[Eric W]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet20​

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

<tbody>
</tbody>

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.

 

[FDibbins]

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

nice example

 

[boxguru]

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

<tbody>
</tbody>

<tbody>
</tbody>