| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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  -- 1image ------------------------------------------------------------------------ 1image's Profile: http://www.excelforum.com/member.php...o&userid=28203 View this thread: http://www.excelforum.com/showthread...hreadid=477455 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 -- 1image ------------------------------------------------------------------------ 1image's Profile: http://www.excelforum.com/member.php...o&userid=28203 View this thread: http://www.excelforum.com/showthread...hreadid=477455 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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  +-------------------------------------------------------------------+ |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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 +-------------------------------------------------------------------+ |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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
| Create new excel table with filtered records from another table | Excel Worksheet Functions | |||
| create pivot table from excel table | Excel Worksheet Functions | |||
| How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel | |||
| create a source table from a special pasted value dynamic crossed table | Excel Programming | |||
Linear Mode
