Formula needed for volume discount pricing

[franklinprints]

Hi everyone,

I own a printing business and there are a TON of variables in print pricing lol.

So I need a formula for multi-page booklets (like catalogs)

Basically I will put a formula in that will come up with the number of prints it will take to make a multi-page booklet and I need the charge per print to be based on cells that will have volume discount tiers. For example these cells off to the right or wherever will look something like this:


[TABLE="width: 500"]
<tbody>[TR]
[TD]1-499[/TD]
[TD]$0.60[/TD]
[/TR]
[TR]
[TD]500-999[/TD]
[TD]$0.50[/TD]
[/TR]
[TR]
[TD]1000-2499[/TD]
[TD]$0.40[/TD]
[/TR]
</tbody>[/TABLE]

Within the formula that determines the number of prints needed will need to be something telling it to pull pricing BASED ON THE quantity of prints in the volume brackets shown above.

I hope this isn't too super confusing. Feel free to let me know any questions.

 

[AhoyNC]

You could use a look up table:
Note: Min values for pages need to be in ascending order.

Excel Workbook
	A	B
1	Pages	628
2	discount	0.5
3
4	Lookup Table
5	Min
6	1	$0.60
7	500	$0.50
8	1000	$0.40
Sheet

 

[Logit]

.


[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]

From​

[/td][td]

To​

[/td][td]

Cost Per​

[/td][td]

Total Ordered​

[/td][td]

Total Cost​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

1​

[/td][td]

499 ​

[/td][td]

$0.60​

[/td][td]

355​

[/td][td]

$213.00​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

500​

[/td][td]

999 ​

[/td][td]

$0.50​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

1000​

[/td][td]

2,499 ​

[/td][td]

$0.40​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td][/td][td]Formula in E2 : =IF(D2<500,D2*0.6,IF(D2<1000,63*0.5,IF(D2<5000,)*63*0.4))[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[franklinprints]

THANK YOU I will try this!

 

[Logit]

You are welcome.