Array formula query

[L0ndonbound]

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

 

[Eric W]

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

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	WEEK	SALES	INVENTORY	SHIPPED		0-1 WEEKS	1-2 WEEKS	2-3 WEEKS	3-4 WEEKS	4-5 WEEKS	5-6 WEEKS		TOTAL
2	1	5	5	0		5	0	0	0	0	0		5
3	2	6	11	0		6	5	0	0	0	0		11
4	3	7	18	0		7	6	5	0	0	0		18
5	4	0	18	0		0	7	6	5	0	0		18
6	5	3	21	0		3	0	7	6	5	0		21
7	6	5	21	5		5	3	0	7	6	0		21
8	7	0	16	5		0	5	3	0	7	1		16
9	8	0	13	3		0	0	5	3	0	5		13
10	9	2	10	5		2	0	0	5	3	0		10
11	10	0	10	0		0	2	0	0	5	3		10
12	11	5	10	5		5	0	2	0	0	3		10
13	12	0	5	5		0	5	0	0	0	0		5
14	13	0	0	5		0	0	0	0	0	0		0

<tbody>
</tbody>

<p

Sheet1</p>

Worksheet Formulas Cell Formula 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]

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

 

[Eric W]

I'm glad it works for you.