Price points invoice

[Reec0n]

Hi all,

I’m building an invoice and for two products.

If someone buys 1 - 30 of Product 1 it’s £300 per product.

31 - 60 its £250.

60+ it’s £200.

Is there a way to automate the sum total amount to reflect the discounted rates.

Ie. Qty 40 should equal 10,000

Any help would be greatly appreciated.

Thanks in advance.

 

[DRSteele]

This is best achieved using a price table and function VLOOKUP. Like this:

	A	B	C
purchase
cost

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] "]hurdle[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] "]price[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]300[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]31[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]250[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]61[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]200[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]40[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]10000[/TD]

</tbody>

Sheet21​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C7[/TH]
[TD="align: left"]=VLOOKUP(C6,A2:B4,2,1)*C6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Reec0n]

This is best achieved using a price table and function VLOOKUP. Like this:

	A	B	C
hurdle	price
purchase
cost

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]40[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10000[/TD]

</tbody>

Sheet21​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10"]C7[/TH]
[TD="align: left"]=VLOOKUP(C6,A2:B4,2,1)*C6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks this works great.

Question 2: I am invoicing for two products so the first price points are for product one only. How do I associate product1 with this price point and do the same for product2?

I've managed all the basics of VAT% and single price as well. I will post the full sheet for anyone else's invoicing needs once complete.

Again any help is greatly appreciated.

Thanks

 

[DRSteele]

Well, I don't know what a 'price point' is...sounds like slang.

I think what you're after involves simply adding a column for Product2 to the price table or creating a second price table for Product2 if it has different hurdles. Here is an algorithm for the simpler case. Copy D19 downwards.

	A	B	C	D
Example	purchase	cost
P1
P2
P2
P1
P2

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] "]Prices[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F4B084]#F4B084[/URL] , align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] "]hurdle[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] "]P1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] "]P2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]300[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1100[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]31[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]250[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]400[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]61[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]200[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]

[TD="align: right"]40[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]10000[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]

[TD="align: right"]75[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]7500[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]2200[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]1800[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]

[TD="align: right"]52[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]20800[/TD]

</tbody>

Sheet21​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D19[/TH]
[TD="align: left"]=VLOOKUP(C19,$A$14:$C$16,MATCH(B19,$A$13:$C$13,0),1)*C19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Reec0n]

This worked really well and at scale. Thank you very much.

FYI: https://www.google.co.uk/search?q=p...69i60l3j0l2.2027j1j7&sourceid=chrome&ie=UTF-8