Array formula for Tiered pricing and Allocation for multiple locations

I revised the formulas in the first three columns of the "Trying to Automate" table to produce what it appeared you were trying to achieve. Your description wasn't completely clear (you mentioned "tier limit" which wasn't a term used in the worksheet).

This formula:

{=IFERROR(INDEX($I$5:$I$13,MATCH(1,(A25=$F$5:$F$13)*(B25=$G$5:$G$13&" "&"Total"),0)),"")}

Select allOpen in new window

in column I seemed to try to combine information with the match formula and it was resulting in errors. I think the SUMPRODUCT approach is more straightforward than trying to use INDEX/MATCH on multiple fields.
Excess-Commit---Allocation-file-per.xlsx

HI Doug,

I reviewed the sumproduct formula, but found that Customer B country of Japan has two prices and it shows 20 and 20 in row G37 and G38.

I think we need to tweak it a little bit or we can add a helper column or something.

I'm sorry for not being very clear. Lets set aside for a min the worksheet and see if I can explain what I'm trying to accomplish in this project conceptually.

My concept closely relates to that of a cellphone bill tier pricing. If we think in the same context, every month I have certain minutes that I could use at not additional charge, once passed or reached the tier free limit, I would pay a fixed amount ( 2cts ) per minute for the next 500 min. If I reached the next 500 min limit, the additional 1000 or whatever min I incurred will cost me 5cts.

Summary

If I have a phone in the USA

USA-Pricing
Minutes            Price       Description
0        - 4000         0          Fixed Monthly price
4000 - 4500       2cts        Next 500 min
4500 - XXXX       5cts        Thereafter with no limit
 
If I have another phone in Japan

Japan Pricing
Minutes            Price       Description
0        - 1000         0          Fixed Monthly price
1000 - 2500       1cts        Next 500 min
2500 - XXXX       10cts        Thereafter with no limit

So, the goal is to calculate the additional minutes over the Fixed Monthly amount and multiply it times its price shown to arrive at the excess amount.

I have used a pivot table because it summarizes the data by country. I wasn't sure if the running total in the pivot table will help for the tier pricing calculation range.

The second goal is to allocate the excess amount based on the ratio percentage of each location per country.

I did modify the attached file by adding a price list that resembles the summary pricing described above.

I'm open to modify the pricing list if it helps with the formula automation.
Excess-Commit---Allocation-file-per.xlsx

This is helpful. I won't be able to revisit this until later today but your explanation makes it much clearer.

Thanks Doug

I think I have found a way to calculate the additional minutes over the fixed monthly amount as discussed in my concept above. The key to this is to calculate the differential rate for each country.

Per the example above, I will use the USA Pricing table below. The answer is to add additional column call Price Differential, then use sumproduct to do the math.

 USA-Pricing
Minutes            Price       Description                              Price differential
0        - 4000         0          Fixed Monthly price                   0
4000 - 4500       2cts        Next 500 min                              2  
4500 - XXXX       5cts        Thereafter with no limit            3
 
Once the price differential is calculate for each country, then we can use the sumproduct formula:

=sumproduct(--(totalamount>{0,4000,4500}),(totalamount-{0,4000,4500}),PricedifferentialRange)

Then I used a second pivot table for the allocation method based on the location ratio.

Now, I need a formula to calculate the price differential Key Column (J6:J14) in my attached worksheet. if this can be automated, then I think I got it.

Please let me know your thoughts

I thought I had uploaded the file in my previous post, then I realized I didn't. I'm attaching the latest file with the changes.
Excess-Commit---Allocation-file-per.xlsx

Just now getting a chance to revisit. The two-level pricing for Japan puts a bit of a twist into it but it still seems like there should be a solution that is more straightforward.

Are you thinking of hard coding the minute tiers into the sumproduct formula for each country?

I'm not quite with you on the price differential aspect.

HI Doug,

I'm thinking to maintain the pricing list by entering the minutes threshold, but I will need a formula to calculate the price differential at each country. In the attached spreadsheet, the price differential is manually entered because the formula is not there yet.

If there are alternatives to arrive at the same solution, then I'm willing to change it or modify it.

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.