Create Discout Table
 

Hi, i'm trying to create a discount table in a workbook with 4 sheets
products, customers, discount, invoice. My problem is i'm not very
experienced and i'm not shore of how to write the formula. The discount
% i need Vlookup to calculate on the invoice refers to the amount of qty
purchased ie:
0<6= 0% disc, 5<11= 2% disc, there are five in total to calculate.
I have tried the If statement but when i use Vlookup to return the
value on the invoice it only shows 10% for all quantities. Can someone
pls show me how i would write the formula.

Many thanks in advance
:confused:


--
1image
------------------------------------------------------------------------
1image's Profile: http://www.excelforum.com/member.php...o&userid=28203
View this thread: http://www.excelforum.com/showthread...hreadid=477455

Create Discout Table
 

Hi,

Discount sheet:
purchased quantity (PQ), Discount (D)
0= PQ (A2) D: 0% (B2)
0< PQ< 6 (A3) D: 0% (B3)
6<=PQ<11 (A4) D: 2% (B4)
11<=PQ<?? (A5) D: ?% (B5)
....

Formula in Invoice!B2 (if PQ is in column A)

=VLOOKUP(A2;Discount!$A$2:$B$6;2)

Regards,
Stefi

€ž1image€� ezt Ã*rta:


Hi, i'm trying to create a discount table in a workbook with 4 sheets
products, customers, discount, invoice. My problem is i'm not very
experienced and i'm not shore of how to write the formula. The discount
% i need Vlookup to calculate on the invoice refers to the amount of qty
purchased ie:
0<6= 0% disc, 5<11= 2% disc, there are five in total to calculate.
I have tried the If statement but when i use Vlookup to return the
value on the invoice it only shows 10% for all quantities. Can someone
pls show me how i would write the formula.

Many thanks in advance
:confused:


--
1image
------------------------------------------------------------------------
1image's Profile: http://www.excelforum.com/member.php...o&userid=28203
View this thread: http://www.excelforum.com/showthread...hreadid=477455

Create Discout Table
 

thanks Stefi
it's 11pm at night here
i'll try it in the morning
i do believe it's a gonna work
thanks a million :

--
1imag
-----------------------------------------------------------------------
1image's Profile: http://www.excelforum.com/member.php...fo&userid=2820
View this thread: http://www.excelforum.com/showthread.php?threadid=47745

Create Discout Table
 

You are welcome! Waiting for the result!
Stefi


€ž1image€� ezt Ã*rta:


thanks Stefi
it's 11pm at night here
i'll try it in the morning
i do believe it's a gonna work
thanks a million :)


--
1image
------------------------------------------------------------------------
1image's Profile: http://www.excelforum.com/member.php...o&userid=28203
View this thread: http://www.excelforum.com/showthread...hreadid=477455

Create Discout Table
 

I ended up using a more simplified version that someone else shared with
me,
it goes like this

QTY DISCOUNT vlookup works like this =VLOOKUP(D19,DTABLE,2).
0 0%
5 2%
10 5%
20 8%
50 10%

!your help and support was much appreciated!.

I have another question that i hoped you might help me with i have
attached a screen shot of the invoice to help explain what i mean, i
have copied a formula down the description list and no matter what i do
i can't get the 3rd item to display the correct description :rolleyes:


+-------------------------------------------------------------------+
|Filename: invoice problem.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=3926 |
+-------------------------------------------------------------------+

--
1image
------------------------------------------------------------------------
1image's Profile: http://www.excelforum.com/member.php...o&userid=28203
View this thread: http://www.excelforum.com/showthread...hreadid=477455

Create Discout Table
 

Sorry, I couldn't find out the cause of the problem from the picture. Perhaps
if you attach the invoice and product files themselves!

Stefi


€ž1image€� ezt Ã*rta:


I ended up using a more simplified version that someone else shared with
me,
it goes like this

QTY DISCOUNT vlookup works like this =VLOOKUP(D19,DTABLE,2).
0 0%
5 2%
10 5%
20 8%
50 10%

!your help and support was much appreciated!.

I have another question that i hoped you might help me with i have
attached a screen shot of the invoice to help explain what i mean, i
have copied a formula down the description list and no matter what i do
i can't get the 3rd item to display the correct description :rolleyes:


+-------------------------------------------------------------------+
|Filename: invoice problem.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=3926 |
+-------------------------------------------------------------------+

--
1image
------------------------------------------------------------------------
1image's Profile: http://www.excelforum.com/member.php...o&userid=28203
View this thread: http://www.excelforum.com/showthread...hreadid=477455