Percent Calculation on Table for drop down boxes

[ChrisHennefer]

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!

 

[etaf]

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 ?

 

[ChrisHennefer]

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?

 

[etaf]

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

Where is the discount amount located ?

 

[ChrisHennefer]

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

 

[etaf]

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 %)

 

[ChrisHennefer]

Yes

 

[etaf]

what does yes mean
it now all works OK

 

[ChrisHennefer]

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

 

[etaf]

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%

Then in your other sheet
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