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?

calculate/convert volume price to monthly average price
 

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" wrote in message
...
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?

calculate/convert volume price to monthly average price
 

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" wrote in message
...
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?