Expanding unit quantities into a full list of units & OF

[atr]

I've got a spreadsheet (See img below) with item sales. An item is sold in one month, and the income from that sale is received in that month or a later month. What I need to figure out is how to properly calculate income based on sale prices from the past.

Thanks for your help!

Line 13 shows what the calculated values ought to be.

 

[expresso]

solve with array?

Maybe you can transform your sales data into:

sale # price
1 10
2 10
3 10
4 15
5 15
etc. etc.

and work from this array

 

[atr]

Good suggestion. I think if I had the data in that form, I could use DSUM to add together the appropriate sales prices. Now, how to get the data in that form . . .

 

[atr]

Thinking a bit more about this, I guess I could use OFFSET instead of DSUM to get the ranges for the sums I need. This still leaves the question of how to expand quantities into a full list, e.g.

<table border=1>
<tr><td>Qty.</td><td>price</td></tr>
<tr><td>2</td><td>$4</td></tr>
<tr><td>1</td><td>$5</td></tr>
<tr><td>1</td><td>$4</td></tr>
<tr><td>3</td><td>$6</td></tr>
</table>

Needs to be converted to:
<table border=1>
<tr><td>Qty.</td><td>price</td></tr>
<tr><td>1</td><td>$4</td></tr>
<tr><td>1</td><td>$4</td></tr>
<tr><td>1</td><td>$5</td></tr>
<tr><td>1</td><td>$4</td></tr>
<tr><td>1</td><td>$6</td></tr>
<tr><td>1</td><td>$6</td></tr>
<tr><td>1</td><td>$6</td></tr>
</table>

or simply:
<table border=1>
<tr><td>price</td></tr>
<tr><td>$4</td></tr>
<tr><td>$4</td></tr>
<tr><td>$5</td></tr>
<tr><td>$4</td></tr>
<tr><td>$6</td></tr>
<tr><td>$6</td></tr>
<tr><td>$6</td></tr>
</table>

 

[atr]

FWIW, I was able to craft a solution based on the progressive pricing formula here:
http://www.cpearson.com/excel/pricing.htm