1 formula for 2 discount % and 3 countries

Salamullah

Board Regular
Joined
Mar 28, 2011
Messages
215
Area
Price
Discount 1
Discount2
Net price
A
100
20.5
3.78
79.5
B
100
15.7
2.95
84.3
C
100
11.3
1.87
88.7
A
20.5%
4.75%
B
15.7%
3.5%
C
11.3%
2.105%

<tbody>
</tbody>

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

Board Regular
Joined
Aug 7, 2008
Messages
86
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

Board Regular
Joined
Aug 7, 2008
Messages
86
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

Board Regular
Joined
Aug 7, 2008
Messages
86
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

Board Regular
Joined
Mar 28, 2011
Messages
215
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
 

Forum statistics

Threads
1,082,612
Messages
5,366,610
Members
400,906
Latest member
incanus

Some videos you may like

This Week's Hot Topics

Top