HELP required - cannot create formula to auto calculate tiered pricing...

**dcj1606** · 02-15-2012, 11:41 AM

Hi All,

I'm fairly new to this forum so go easy on me! i'm also no excel wizard so i'm hoping you can help...

I'm trying to create a fairly basic pricing model based on volume tiers of licenses purchased. The more licenses purchased the cheaper the incremental license price - however, the price per license is reduced for those bought that break into the new tier - i.e tier 1 = 1-100 so 50 licenses = 50 x tier 1 price. If another 100 are purchased then 50 will be at tier 1 pricing but only 50 will then go to tier 2 pricing and so on...

I've attached the xls i'm working on and what i want to do is show a month by month example of what the costs would be if the volume of licenses increased each month by 250.

Please could someone advise on the types of formula needed to do this? i've had a look and see some examples but i can't find any that show what i need.

Thanks
DavidModelling_tiered pricing.xlsx

**Dionysos** · 02-15-2012, 12:58 PM

I added a couple more columns to your pricing table to make the calculations easier. Since the price isn't reduced until the next tier is broken, is it good to keep track of what the max and cummulative price would be for each given tier. Basically what the formula does is to identify which row of you pricing table that the number of licenses falls within and then takes the cummulative amount up to that tier and adds to that the incremental licenses times the per tier license cost. I have attached your revised spreadsheet.