Determining a price based on a range of values

Was hoping I could find some help on this as I'm not an advanced Excel user.

I am setting up a contract based on volume tiers over a period of 5 years. Each Tier has a range of units. Based on the amount of product purchased, the price may change based on the tier.

As a simple example:

Tier 1, 0-9 units, price of $100
Tier 2, 10-19 units, price of $90
Tier 3, 20-29 units, price of $85
Tier 4, 30-39 units, price of $80
Tier 5, 40-50 units, price of $75

Using the example above, if I enter a unit volume of 25 units I need for the spreadsheet to report back a unit price of $85.

I need to create the spreadsheet in such a way that I can manipulate the volume tiers (for example change the Tier 0-9 units to 0-14 units) to see how it impacts the entire contract.

Any help would be appreciated. Thanks!

You could also set up a linear equation for it to interpolate prices based on new unit amounts. But if your prices are affected on available package sizes (say one box holds 10, if the customer orders 11, you still need 2 boxes), or other variables, you can easily run into problems.

Thanks for the feedback!

I ended up being able to use an IF statement to fix the problem (thankfully I didn't have more than 7 tiers for pricing).

=IF(E43<=F9,G9,IF(E43<=I9,J9,IF(E43<=L9,M9,IF(E43<=O9,P9,IF(E43<=R9,S9,"ERROR")))))

The first part queries whether the volume falls in tier 1, if not it queries Tier 2 (having already excluded the lower volume in Tier 1), etc.