Demand coverage

[SimonaB]

hello everyone,

I am trying to calculate the stock coverage in numbers of months and I haven't figured out how to do this without a macro.

My data is something like this, with stock available only for the current month and I would like to see how many months would this figure would cover in terms of demand.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]months[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]demand[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD]82[/TD]
[TD]23[/TD]
[TD]44[/TD]
[TD]52[/TD]
[TD]25[/TD]
[TD]14[/TD]
[TD]78[/TD]
[TD]23[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]stock[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]stock coverage (in months)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I would kindly appreciate any input from your side.

Thanks!

 

[Special-K99]

in B4 (blank cell below 40)
=IF(SUM($B2:B2)<=$B$3,"Y","N")
copy across to M4

Y inidcates stock is covered, N inidcates not enough stock

 

[bhos123]

you can add a row below the demand with cumulative demand, = Sum(B$2:$B$2)

then use the below formula to get the months : =LOOKUP(G9,G8:J8,G7:J7)

where G9=40
G8:J8 is the cumulative demand data range
G7:J7 is the months range.

The result would be number of months you want to see.