Formula for exact weeks on hand

[brain.010]

Hi there, I'm trying to do a formula to tell me how many weeks of supply I have based on a forecast, without using averages.

I want to avoid 'on hand / average week of sales'.

Right now in my data tab I have a simple columns view to see when inventory will run out by item. What formula would I use to count how many weeks of inventory (to the decimal) I have before running out?

The yellow column is where the formula will go, and the 4/13-6/29 is the forecast inventory levels. So as an example, SKU 1 would have ~2.5 weeks on hand, and the week of 4/27 is when they would run out.

I appreciate any help on this, I've been struggling with it for a week..

[Bernie Deitrick]

I want to avoid 'on hand / average week of sales'

Well, you can't - that is basically what forecasting does.

But try this version

[Bo_Ry]

Please try at I3
=IFNA(MATCH(0,INDEX(-L3:W3,)),)+1/(1-IFNA(LOOKUP(0,-L3:W3,M3:W3),L3)/IFNA(LOOKUP(0,-L3:W3,L3:W3),H3))

Decimal part is calculated from the latest positive Hand on divide by the latest Forcast

[brain.010]

Beautiful, Bo_Ry! Thank you!