Calculating current cover based on forward requirements...

[Matty]

Hello All,

This should be fairly straight forward, but I'm struggling to construct a formula to give me the result I want.

I have drawn up a simple summary to show what I am trying to achieve:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		Week1	Week2	Week3	Week4	Week5	Week6	Week7	Week8	Week9	Week10
2	Demand	10	15	12	8	16	20	13	15	7	25
3	Production	0	25	0	0	25	0	25	0	25	0
4	Stock	35	25	35	23	15	24	4	16	1	19
5	Cover(Days)	?	?	?	?	?	?	?	?	?	?
Sheet1

I want to calculate how much cover, in days, the current Stock position gives me, based on the forward Demand that exists. If the days cover is actually negative, the formula should treat it as such.

I'd greatly appreciate any suggestions people have got.

Thanks,

Matty

 

[Legacy 452786]

Awesome solution 12 years ago to this real-life problem of calculating stock coverage! Works correctly even with 0 weeks.

The issue is the OFFSET which is volatile.

Anyone who is able to change OFFSET TO INDEX?

I tried replacing it with INDEX construction

B2:INDEX(B2:$K2,,COLUMN(B2:$K2)-COLUMN(B2)+1)

But INDEX doesn't accept an array for [column num].

I tried solutions based on:
https://excelxor.com/2014/09/05/index-returning-an-array-of-values/
https://stackoverflow.com/questions/47187863/can-excels-index-function-return-array

Using the coercion

B2:INDEX(B2:$K2,,N(IF({1}, COLUMN(B2:$K2)-COLUMN(B2)+1)))

But this doesn't work either. Based on evaluate formula, INDEX then returns an array of values, not an array of references, and then generates #VALUE ! error when combined with the : operator.

Any ideas?

 

[Legacy 452786]

Non-volatile solved!

I managed to design a calculation that delivered the same result as Dominic's very clever but volatile solution from 2007.

Downside is that three helper rows/columns are needed.

1. Cumulative Demand (ascending running total of the demand)
2. Weeks Full Coverage (array formula using MATCH that searches cumulative demand to find the week when the current stock doesn't fully cover demand anymore)
3. Week Partial Coverage (non-array formula using [cell]:INDEX[cell] dynamic range construction to find the residual stock for the partially covered week and calculate the partial coverage of that week).

Total Coverage Weeks = Weeks Full Coverage + Week Partial Coverage

I have a spreadsheet comparing the different calculations. I get the same result with the original input data.

If anyone is truly interested, please let me know and i'll post it. I'm also subscribing to this thread.