# Array formula query

#### L0ndonbound

##### New Member
Hi,

I want to create an array formula to count the number of weeks that inventory is held in stock - as per the table to the right. What formula is necessary to count this?

 WEEK SALES INVENTORY SHIPPED 0-1 WEEKS 1-2 WEEKS 2-3 WEEK 3-4 WEEKS 4-5 WEEKS 5-6 WEEKS TOTAL 1 5 5 0 5 0 0 0 0 0 5 2 6 11 0 6 5 0 0 0 0 11 3 7 18 0 7 6 5 0 0 0 18 4 0 18 0 0 7 6 5 0 0 18 5 3 21 0 3 0 7 6 5 0 21 6 5 21 5 5 3 7 6 0 0 21 7 0 16 5 0 5 3 7 1 0 16 8 0 13 3 0 0 5 3 5 0 13 9 2 10 5 2 0 5 3 0 0 10 10 0 10 0 0 2 0 5 3 0 10 11 5 10 5 5 0 2 0 3 0 10 12 0 5 5 0 5 0 0 0 0 5 13 0 0 5 0 0 5 0 0 0 5

<colgroup><col span="2"><col><col span="2"><col><col><col><col span="3"><col><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>

Regards,

LB

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Eric W

##### MrExcel MVP
It doesn't require an array formula, but it is a bit hard to wrap your head around. Try:

ABCDEFGHIJKLMN
1WEEKSALESINVENTORYSHIPPED0-1 WEEKS1-2 WEEKS2-3 WEEKS3-4 WEEKS4-5 WEEKS5-6 WEEKSTOTAL
215505000005
32611065000011
43718076500018
54018007650018
65321030765021
76521553076021
87016505307116
98013300530513
109210520053010
1110010002005310
1211510550200310
13120550500005
14130050000000

<tbody>
</tbody>
<p

Sheet1</p>

Worksheet Formulas
CellFormula
F2=B2
G2=MAX(SUM(F1)-MAX(\$D2-SUM(G1:\$K1),0),0)
M2=SUM(F2:K2)

<tbody>
</tbody>

<tbody>
</tbody>

The the F2 formula in and copy down the column. Put the G2 formula in and copy down and across to K14. Put the M2 formula in and copy down.

I assume you're using a FIFO type of logic. If you look closely, the values I have vary from your example on rows 7-12 and 14. I believe mine are correct. If you look at row 6, there were 5 items in inventory in the 4-5 week column. With 5 shipped on row 7, those 5 use up the previous 5, so nothing moves to the 5-6 week column. The other values from F6:I6 are moved to G7:J7.

Let me know if I understand your logic correctly, and if this works for you.

#### L0ndonbound

##### New Member
Eric, thank you. Works a treat - and yes it was FIFO logic.

#### Eric W

##### MrExcel MVP
I'm glad it works for you.

Replies
12
Views
162
Replies
11
Views
110
Replies
14
Views
107
Replies
2
Views
42
Replies
3
Views
61