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

dw970906

New Member
Joined
Mar 5, 2021
Messages
10
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
Joined
Mar 5, 2021
Messages
10
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
Joined
Oct 24, 2012
Messages
4,790
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Mar 5, 2021
Messages
10
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
Joined
Oct 24, 2012
Messages
4,790
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Mar 5, 2021
Messages
10
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)
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top