How can I enter a value and obtain an average based upon tiers.

[M Purvis]

Monthly
Volume Price per Unit
Tier 1 100,000 $0.400
Tier 2 200,000 $0.300
Tier 3 300,000 $0.200


What formula would I use to determine what the average price would be based
upon entering a specific number in a cell, such as 250,000?

I want to create a pricing calculator, in which I can enter the volume for
the month and it creates what the price is based upon the previously stated
tiers.

Many thanks,

Matt

 

[Max]

One way is via VLOOKUP

Supposing this table below is in Sheet1, in A1:C4

Tier 1 100,000 $0.400
Tier 2 200,000 $0.300
Tier 3 300,000 $0.200

In Sheet2

In A1 is the volume: 250000

To get the tier unit price for the volume in A1, put in say, B1:
=IF(A1="","",VLOOKUP(A1,Sheet1!$B$2:$C$4,2,1))

Format B1 as currency

Copy B1 down to retrieve other prices
for other volumes in A2 down

 

[Max]

And a more involved interp of your post
would point to JE's page at:
http://www.mcgimpsey.com/excel/variablerate.html