Array formula query

L0ndonbound

New Member
Joined
Feb 16, 2017
Messages
15
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?

WEEKSALESINVENTORYSHIPPED0-1 WEEKS1-2 WEEKS2-3 WEEK3-4 WEEKS4-5 WEEKS5-6 WEEKSTOTAL
15505000005
2611065000011
3718076500018
4018007650018
5321030765021
6521553760021
7016505371016
8013300535013
9210520530010
10010002053010
11510550203010
120550500005
130050050005

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

<tbody>
</tbody>

Regards,

LB
 

Some videos you may like

Excel Facts

Links? Where??
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
Joined
Aug 18, 2015
Messages
10,219
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,910
Messages
5,525,583
Members
409,651
Latest member
Quasar Hunter

This Week's Hot Topics

Top