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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"M Purvis" <M Purvis@discussions.microsoft.com> wrote in message
news:62D6B1B2-FDE1-4233-AC12-2BD7E179FF3F@microsoft.com...
> 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]

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----