Commit Tier Pricing Calc with Excess Over Commits allocated to multiple locations.

Hi All,


I'm working on a spreadsheet with a tier pricing structure based on country; in some instances multiple pricing within same country. The goal is to compute or calculate the excess commit (limit) at an agreed price, then allocate the computed amount (excess amount) over multiple locations within each country.

The attached file contains a data set showing a few records for multiple customers, the tier pricing data for each customer and country is also available.

What I'm trying to figure it out is to come up with a formula that can find the tier limit per region (e.g., sumproduct, index, array, etc) and retrieve the limit. A second formula will retrieve the price in the same manner the limit was looked up, and lastly a formula that computes the excess amount and allocates it based on a location or multiple locations for the country.

... My first thought was to start of with a pivot table because of the summary, then run a lookup for the tier pricing with array formulas, etc.

Please feel free to brainstorm.