# Expanding unit quantities into a full list of units & OF

#### atr

##### New Member
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.

Line 13 shows what the calculated values ought to be.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### expresso

##### New Member
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

##### New Member
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

##### New Member
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>

Replies
1
Views
287
Replies
0
Views
179
Replies
1
Views
300
Replies
0
Views
578
Replies
1
Views
246

1,196,013
Messages
6,012,856
Members
441,735
Latest member
melastro

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back