# Percent Calculation on Table for drop down boxes

#### ChrisHennefer

##### New Member
I'm making a 'proposal' worksheet. I have a drop down box for the parts, then in the next column a vlookup for the price associated with that part.

I need to add 'discounts' - so from this drop down box they can choose discount and it will calculate the discount on whatever the part they chose before [in the row above]. Can you tell me how to make this formula for the price in my drop down box table?

Thank you!

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
i'm assuming its set out like this

A2 dropdown for product
B2 vlookup retail price
C2 dropdown for discount amount
D2 calculate the new price = b2*(1-c2)

is that correct ?

No, it's currently setup:

A2 Quantity
B2 Dropdown box for product
C2 vlookup for retail price
D2 Calculate extended price (A2*C2)

The discount would be on row below with the discount being in column B [as part of the drop down box] and the formula would be in the vlookup table [like the prices] calculating based on the row above.

Is that possible?

i assume that works OK and gives the total retail price * the quantity

Where is the discount amount located ?

The discount would located in the vloookup table with the other parts.

then you should be able to lookup and apply

A2 Quantity
B2 Dropdown box for product
C2 vlookup for retail price
D2 Calculate extended price (A2*C2)

so you need to be able to lookup the discount
D2* (1 - vlookup discount %)

Yes

what does yes mean
it now all works OK

then you should be able to lookup and apply

A2 Quantity
B2 Dropdown box for product
C2 vlookup for retail price
D2 Calculate extended price (A2*C2)

so you need to be able to lookup the discount
D2* (1 - vlookup discount %)

~~~~~~~~~~~~
so you need to be able to lookup the discount = Yes

D2* (1 - vlookup discount %) = If this is the formula I don't understand it.

??? Thanks, Chris

you want to apply a discount for the product

in your table you said you had for each product a discount amout

Assuming this is the reference table 1
A1 Product_1
B1 Retail price £100
C1 Discount 15%

as an example
A2 Quantity = 5
B2 Dropdown box for product = product_1
C2 vlookup for retail price = Vlookup(B2, RangeOfTable, 2, false) returns £100
D2 Calculate extended price (A2*C2) = 100 * 5 = 500

now we need to discount by 15%
which means the price is 85% of the total 100-15

SO E2 would need to lookup the % value
=
Vlookup(B2, RangeOfTable, 3, false)
returns 15 or 0.15 (as its a %)
then 1-
Vlookup(B2, RangeOfTable, 3, false)
=85% or 0.85
* by the total price
D2* (
1-Vlookup(B2, RangeOfTable, 3, false))
= 500*0.85
= £425

does that explain it - and is that what you required

Replies
9
Views
325
Replies
4
Views
173
Replies
1
Views
251
Replies
1
Views
176
Replies
1
Views
164

1,203,236
Messages
6,054,298
Members
444,715
Latest member
GlitchHawk

### 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.

### Which adblocker are you using?

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

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