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

#### dw970906

##### New Member
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

##### New Member
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

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### etaf

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

##### New Member
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)

Replies
6
Views
125
Replies
7
Views
170
Replies
10
Views
250
Replies
34
Views
552
Replies
2
Views
368

1,136,275
Messages
5,674,787
Members
419,524
Latest member
helensesc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back