1 formula for 2 discount % and 3 countries

Salamullah

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

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Dan5977

Board Regular
Joined
Aug 7, 2008
Messages
100
Office Version
  1. 2010
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
100
Office Version
  1. 2010
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
100
Office Version
  1. 2010
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
221
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top