Formula for tiered pricing (adding a quantity for tier) that includes a free tier

[dw970906]

I am trying to calculate the # of tiers required based on an inputted #. I am using a formula with MOD, but having issues getting the proper # of each tier due to the fact that I am including a "free" amount. I also need to chose the most appropriate tiers for cost effectiveness.

Tiers:
tier 1 = 500 units ($0) This tier is ALWAYS included
tier 2 = 500 units ($100 per unit)
tier 3 = 1000 units ($80 per unit)
tier 4 = 5000 units ($50 per unit)
tier 5 = 10000 units ($20 per unit)

Example 1: 1000 units
- tier 1 will have quantity of 1 (500 units @ $0/unit)
- tier 2 will have a quantity of 1 (500 units @$100/unit = $50,000)
Total cost - $50,000

Example 2: 5000 units
- tier 1 will have a quantity of 1 (500 units @ $0/unit)
- tier 2 will have quantity of 0
- tier 3 will have a quantity of 0
- tier 4 will have a quantity of 1 (5000 units @ $50/unit)
Total cost is $250,000)

In this example, it is cheaper to purchase 1 block of 5000 units (tier 4), vs purchasing 4, tier 3 blocks and 1 tier 2 block.

Any suggestions?

 

[dw970906]

Another example hopefully this clarifies. If I have 6000 units as my input, then I should have the following quantity of tiers:
tier 1 = 1 (500)
tier 2 = 2 (500)
tier 3 = 0
tier 4 = 1 (5000)
tier 5 = 0

The above is what I need to figure out for any arbitrary # I input. The I can easily figure out the cost as I have a static cost per tier. Tier 1 is always going to have a 1 as it is included for free. Hope this helps.

Typo. tier 2 will have a quantity of 1. Apologies

 

[etaf]

ok, so somewhat different to what I thought you wanted, sorry - perhaps a few more examples
8,980
7,551
sort of odd numbers which are not straight multiples of your tiers

 

[dw970906]

Hope these help, thanks. Other examples:

Input # of 8980. The quantity of tiers will look like this:
tier 1 = 1 (500) always included
tier 2 = 1 (500)
tier 3 = 3 (3000)
tier 4 = 1 (5000)
tier 5 = 0 (10000)

Input # of 7551. The quantity of tiers will look like this:
tier 1 = 1 (500) always included
tier 2 = 1 (500)
tier 3 = 2 (2000)
tier 4 = 1 (5000)
tier 5 = 0 (10000)

 

[etaf]

thanks, i'll have to have a think, not sure how to achieve that

your showing ALL lower tiers used, but not in all examples - how are the tiers decided on, not sure i'm understanding still - really sorry
5501

 

[dw970906]

thanks, i'll have to have a think, not sure how to achieve that

your showing ALL lower tiers used, but not in all examples - how are the tiers decided on, not sure i'm understanding still - really sorry
5501

for 5501, tier 1 = 1, tier 2 = 1 and tier 4 = 1. Not all tiers would be utilized. Best fit for tiers.
For best fit quantity of tiers I have this as one of the formulas: =IF((OR(L4>0,AND(L4>0,MOD(L4,A3)=0))),(L4-MOD(L4,A3))/A3,0)
For remainder of quantity that helps populate the tiers I have this as one of the formulas: =IF(L4>=A3,MOD(L4,A3),L4)

 

[Dave Patton]

Please show the brackets and rates that you are using.
If the numbers do not agree, show how you manually calculated the amount.

Commission2022.xlsm
	C	D	E	F
1
2	Units Sold	Sales Price		Dave
3	7,001	6		20,912.91
4	12,305	4		2,889.54
5	2,500	11.4		14,250.00
6	1,001	6		3,003.00
7	5	3		0.90
8	9,500	10		46,875.00
9	7,000	0.6		250.92
10	7,000	6		20,910.00
11	1,000,000	10		4,751,750.00
12	1,000,000	0.04		2,280.84
13
14
15		Brackets	Payout
16
17		0	50.0%	6.00%
18		5,000	49.5%	5.94%
19		6,000	49.0%	5.88%
20		7,000	48.5%	5.82%
21		8,000	48.0%	5.76%
22		9,000	47.5%	5.70%
23
2a

Cell Formulas
Range	Formula
F3:F12	F3	=SUM(IF(C3>aB,(C3-aB)*IF(D3>=5,aR,a2R))*D3)
F18	F18	=$F$17*0.99
F19	F19	=$F$17*0.98
F20	F20	=$F$17*0.97
F21	F21	=$F$17*0.96
F22	F22	=$F$17*0.95

 

[Dave Patton]

Please delete or ignore the previous post.
It went to the incorrect thread.