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

dw970906

New Member
Joined
Mar 5, 2021
Messages
13
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?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,866
Office Version
  1. 365
Platform
  1. MacOS
your 2 examples
1000 , you use the 500 threshold, 1000-500
(500 units @$100/unit = $50,000)
BUT
Example 2
you use 5000 for tier 4 - But should that be 4500 at tier 4 , as you have 500 free and the 5000 brings it to tier 4 , but its only 4500 at the 5000 tier setting
- tier 4 will have a quantity of 1 (4500 units @ $50/unit)

Also the 1000 example - should that use the 80 tier as its 1000
 
Last edited:

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,866
Office Version
  1. 365
Platform
  1. MacOS
does this work for you??
BUT I have assumed the threshold - is based on total volume as per your 2nd example and not with the 500 deducted
But thats a simple modification

Etaf - match values.xlsx
ABCDEFGH
2tier 100QtyTierAmount
3tier 25001004000
4tier 310008010008040000
5tier 4500050500050225000
6tier 51000020100000201990000
7000
Sheet2
Cell Formulas
RangeFormula
G3:G7G3=INDEX($C$2:$C$8,MATCH(F3,$B$2:$B$8,1))
H3:H7H3=INDEX($C$2:$C$8,MATCH(F3,$B$2:$B$8,1))*(F3-500)
 

dw970906

New Member
Joined
Mar 5, 2021
Messages
13
your 2 examples
1000 , you use the 500 threshold, 1000-500
(500 units @$100/unit = $50,000)
BUT
Example 2
you use 5000 for tier 4 - But should that be 4500 at tier 4 , as you have 500 free and the 5000 brings it to tier 4 , but its only 4500 at the 5000 tier setting
- tier 4 will have a quantity of 1 (4500 units @ $50/unit)

Also the 1000 example - should that use the 80 tier as its 1000
In the second example, you are correct, it would be 4500 units, but I will charge 1 unit of 5000. The total available units will be 5500 - I wouldn't bill again until above 5500.
 

dw970906

New Member
Joined
Mar 5, 2021
Messages
13

ADVERTISEMENT

does this work for you??
BUT I have assumed the threshold - is based on total volume as per your 2nd example and not with the 500 deducted
But thats a simple modification

Etaf - match values.xlsx
ABCDEFGH
2tier 100QtyTierAmount
3tier 25001004000
4tier 310008010008040000
5tier 4500050500050225000
6tier 51000020100000201990000
7000
Sheet2
Cell Formulas
RangeFormula
G3:G7G3=INDEX($C$2:$C$8,MATCH(F3,$B$2:$B$8,1))
H3:H7H3=INDEX($C$2:$C$8,MATCH(F3,$B$2:$B$8,1))*(F3-500)

In the worksheet I want to use, I will need to automatically identify the # of tiers, based on my input value. I will then use that to calculate the overall price. How can I automatically identify the # of tiers needed without manually figuring it out?
 

dw970906

New Member
Joined
Mar 5, 2021
Messages
13
Now that I re-read my post, to clarify, I am not charging by unit, but by the # of tiers which the input #. So, in example 2, I will charge for 1 tier at 5000 units and the output price is 5000 * $50. The important piece of this if to identify the # of tiers needed based on the input value, when considering the input value as I mentioned.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,866
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

Not sure i fully understand, SORRY

to get the tier than i added the lookup , based on my current understanding
Which is the qty - decides the tier to USE - but that value is only used for the qty less the free 500 items

Not sure what you would charge if the qty was less than 500

I dont fully understand
n the second example, you are correct, it would be 4500 units, but I will charge 1 unit of 5000. The total available units will be 5500 - I wouldn't bill again until above 5500.
As the 2nd example was 5000 not 5500 and the next tier is not until Tier 5

I have also put the spreadsheet onto a share - but with XL2BB you can copy into a spreadsheet
Anyway - you can try a few different Qty and see if works


Etaf - match values.xlsx
ABCDEFGHI
2tier 100QtyTier NameTier ValueAmount
3tier 250010040tier 100
4tier 31000801000tier 38040000
5tier 45000505000tier 450225000
6tier 51000020100000tier 5201990000
7800tier 210030000
8
Sheet2
Cell Formulas
RangeFormula
G3:G7G3=INDEX($A$2:$A$8,MATCH(F3,$B$2:$B$8,1))
H3:H7H3=INDEX($C$2:$C$8,MATCH(F3,$B$2:$B$8,1))
I3:I7I3=INDEX($C$2:$C$8,MATCH(F3,$B$2:$B$8,1))*(F3-500)
 

dw970906

New Member
Joined
Mar 5, 2021
Messages
13
Thanks. I will take a look. Basically, I want to count how many of each tier needed and I need to know based on the inputted number, i.e. 2500, how many of each tier I would need (also optimizing for the best price per tier) in my example above.

I will only get the overall price by charging on the quantity of tiers (as I will have a list price per tier). Tier 1 (500) is no cost and needs to be included as a baseline. If the starting # is less than 500, then there is no cost to the tier, as this base tier is given free. Attached is a screen shot from my worksheet. So knowing the # of each tier is critical to my calculations.
 

Attachments

  • Screen Shot 2021-03-05 at 2.39.55 PM.png
    Screen Shot 2021-03-05 at 2.39.55 PM.png
    23.3 KB · Views: 3

dw970906

New Member
Joined
Mar 5, 2021
Messages
13
Like I said, the most important # for me is to understand how many of each tier I need and I will then use those amounts to multiple against the cost per tier to get my end price.
 

dw970906

New Member
Joined
Mar 5, 2021
Messages
13
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.
 

Forum statistics

Threads
1,141,075
Messages
5,704,156
Members
421,330
Latest member
imdumb

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