1 formula for 2 discount % and 3 countries

[Salamullah]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Area
[/TD]
[TD]Price
[/TD]
[TD]Discount 1
[/TD]
[TD]Discount2
[/TD]
[TD]Net price
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]100
[/TD]
[TD]20.5
[/TD]
[TD]3.78
[/TD]
[TD]79.5
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]100
[/TD]
[TD]15.7
[/TD]
[TD]2.95
[/TD]
[TD]84.3
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]100
[/TD]
[TD]11.3
[/TD]
[TD]1.87
[/TD]
[TD]88.7
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD][/TD]
[TD]20.5%
[/TD]
[TD]4.75%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD][/TD]
[TD]15.7%
[/TD]
[TD]3.5%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD][/TD]
[TD]11.3%
[/TD]
[TD]2.105%
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Please help to make single formula in Net price column for example in E2.

IF A2 = A
Discount 1 = B2 X C6
Discount 2 = B2 - C2 X D6

IF A2 = B
Discount 1 = B2 X C7
Discount 2 = B2 - C2 X D7

IF A2 = C
Discount 1 = B2 X C8
Discount 2 = B2 - C2 X D8

 

[Dan5977]

I don't understand what you are trying to do with Discount 2. Why is Discount 2 different in cell D2 to cell D6? Your Net price appears to be ignoring Discount 2 and just using Discount 1.

 

[Dan5977]

OK so I have figured out that
Discount 2 = (B2-C2) X D6
Rather than
Discount 2 = B2 - C2 X D6

What do you want the Net price to be? Does it discount by Discount 1 and then by Discount 2? If so then surely it is just =B2-C2-D2
Or are you trying to remove the fields in C2 and D2 and just rely on the table for discounts. Either way it is pretty easy but just need to be clear what you are after.

 

[Dan5977]

For the latter you could use this in cell E2 and copy down.
=($B2-$B2*VLOOKUP($A2,$A$6:$D$8,3,0))-(($B2-$B2*VLOOKUP($A2,$A$6:$D$8,3,0))*VLOOKUP($A2,$A$6:$D$8,4,0))

 

[Salamullah]

For the latter you could use this in cell E2 and copy down.
=($B2-$B2*VLOOKUP($A2,$A$6:$D$8,3,0))-(($B2-$B2*VLOOKUP($A2,$A$6:$D$8,3,0))*VLOOKUP($A2,$A$6:$D$8,4,0))

Excellent !! thanks that's what I want