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
1	hurdle	price
2	1	300
3	31	250
4	61	200
5
6		purchase	40
7		cost	10000

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet21​

Worksheet Formulas Cell Formula C7 =VLOOKUP(C6,A2:B4,2,1)*C6 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Reec0n]

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

	A	B	C
1	hurdle	price
2	1	300
3	31	250
4	61	200
5
6		purchase	40
7		cost	10000

<tbody>
</tbody>

Sheet21​

Worksheet Formulas Cell Formula C7 =VLOOKUP(C6,A2:B4,2,1)*C6 <tbody> </tbody>

<tbody>
</tbody>

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
12		Prices
13	hurdle	P1	P2
14	1	300	1100
15	31	250	400
16	61	200	100
17
18		Example	purchase	cost
19		P1	40	10000
20		P2	75	7500
21		P2	2	2200
22		P1	6	1800
23		P2	52	20800

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet21​

Worksheet Formulas Cell Formula D19 =VLOOKUP(C19,$A$14:$C$16,MATCH(B19,$A$13:$C$13,0),1)*C19 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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