calculate/convert volume price to monthly average price

Let's assume that we have a price that is dependant on volume:
0-1k units: $10
1k-2k units: $9
2k-5k units: $8
5k-10k units:$7
10k-20k units: $6

Now, based on a volume forecast, I want to forecast the average price in a
given month. I started out using IF-formulas (thought that it would be enough
with 5 conditions), but I soon discovered that it was far more complex than
what I anticipated. Is there a nice formula for this (or VBA code) or is
there someone who has an idea on how to attack this problem in a good way?

How will you predict the number of sales at each volume level?
If I thought the percentages would be: 5, 20, 50, 20, 5
I could compute an average with
=SUMPRODUCT({0.05,0.2,0.5,0.2,0.05},{10,9,8,7,6})
The answer, of course, is $8.00 since I used a symmetrical distribution.
Any help?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bultgren" <[email protected]> wrote in message
news:[email protected]...
> Let's assume that we have a price that is dependant on volume:
> 0-1k units: $10
> 1k-2k units: $9
> 2k-5k units: $8
> 5k-10k units:$7
> 10k-20k units: $6
>
> Now, based on a volume forecast, I want to forecast the average price in a
> given month. I started out using IF-formulas (thought that it would be
> enough
> with 5 conditions), but I soon discovered that it was far more complex
> than
> what I anticipated. Is there a nice formula for this (or VBA code) or is
> there someone who has an idea on how to attack this problem in a good way?

Unfortunately the total volume over the year is unpredictable so I can not
use percentages.

"Bernard Liengme" skrev:

> How will you predict the number of sales at each volume level?
> If I thought the percentages would be: 5, 20, 50, 20, 5
> I could compute an average with
> =SUMPRODUCT({0.05,0.2,0.5,0.2,0.05},{10,9,8,7,6})
> The answer, of course, is $8.00 since I used a symmetrical distribution.
> Any help?
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Bultgren" <[email protected]> wrote in message
> news:[email protected]...
> > Let's assume that we have a price that is dependant on volume:
> > 0-1k units: $10
> > 1k-2k units: $9
> > 2k-5k units: $8
> > 5k-10k units:$7
> > 10k-20k units: $6
> >
> > Now, based on a volume forecast, I want to forecast the average price in a
> > given month. I started out using IF-formulas (thought that it would be
> > enough
> > with 5 conditions), but I soon discovered that it was far more complex
> > than
> > what I anticipated. Is there a nice formula for this (or VBA code) or is
> > there someone who has an idea on how to attack this problem in a good way?
>
>
>