IF: Multiple Conditions with Formula Actions

[thewolfdead]

Dear All,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Lot Price[/TD]
[TD]Individual Price[/TD]
[TD]Item[/TD]
[TD]Size[/TD]
[/TR]
[TR]
[TD]$50[/TD]
[TD]formula goes here<formula goes="" here=""><insert formula=""></insert></formula>[/TD]
[TD]Product A[/TD]
[TD]1kg[/TD]
[/TR]
[TR]
[TD]$75[/TD]
[TD]formula goes here<formula goes="" here=""><insert formula=""></insert></formula>[/TD]
[TD]Product B[/TD]
[TD]5kg[/TD]
[/TR]
[TR]
[TD]$100[/TD]
[TD]formula goes here<formula goes="" here=""><insert formula=""></insert></formula>[/TD]
[TD]Product C[/TD]
[TD]10kg[/TD]
[/TR]
</tbody>[/TABLE]

I believe that I have the write multiple IF formula but I keep getting a "#VALUE!" return on the cell. The goal is that depending on the line item @Size, a different calculation is made for its respective @Lot Price:

=IF([@Size]="10kg", ([@[Lot Price]]*(1.5))),IF([@Size]="5kg",([@[Lot Price]]*1.5/3)), IF([@Size]="1kg",([@[Lot Price]]*1.5/6))

I have reviewed it many times and referred to websites...I do not believe that the OR function will assist me.

Any advice would be greatly appreciated.

Regards,
D

 

[jtakw]

Hi,

Not Exactly sure what you're trying to accomplish, I'm just correcting your formula, you're Closed Off your First IF statement before it can go further, so set up your formula to something like this:

Book1
	A	B	C	D
1	Lot Price	Individual Price	Item	Size
2	$50	12.5	Product A	1kg
3	$75	37.5	Product B	5kg
4	$100	150	Product C	10kg
Sheet122

Cell Formulas
Range	Formula
B2	=IF(Size="10kg",Lot_Price*1.5,IF(Size="5kg",Lot_Price*1.5/3,IF(Size="1kg",Lot_Price*1.5/6,"")))

Named Ranges
Name	Refers To	Cells
Lot_Price	=Sheet122!$A$2:$A$4
Size	=Sheet122!$D$2:$D$4

 

[thewolfdead]

Hello jtakw,

That's worked wonderfully. You have correctly anticipated my objective which was to autofill @Individual_Price based on @Lot_Price and @Size.

Thank you very much.

Regards,
D

 

[jtakw]

You're welcome, glad you got it working.