Inventory Cover Formula

Good Morning,

I need a simple formula to calculate months cover of stock vs rolling sales forecast

Thanks

David

Sales Forecast
SKU Description Stock June July August September October November December Month Cover Based on current stock
XXXX XXXX 5650 1200 965 2000 530 892 1500 1000 ???
Month Cover Forum.xlsx

are you after depletion date?

if so put into k4

Please Login or Register  to view this content.

Thanks, is it possible in terms of weeks cover?

please see attached
i amended the month formula to give decimal places instead of a straight month

and weeks cover next to it is based on 30 day months divide by 7
you can fold into formula to make one cell

Perfect, many many thanks

david

It seems to trip over somewhere, please see attached

PLease see attached and ignore previous post

thanks

i know where the error is, i didnt give If statement for over 7 months... but i found inaccuracy in the results as well

i must this is far from simple which is your initial request and maintaining it would be a major PIA
do you need it in single line?

with attachment

try this:
=IF(SUM(D4:J4)>C4,MATCH(TRUE,SUBTOTAL(9,OFFSET($D4:J4,,,,COLUMN($D4:J4)-COLUMN(D4)+1))>C4,0)-1,"MORE STOCKS")

then CTRL-SHIFT-ENTER

Thanks acerrhod, this formula just returned an error or more stock??

you need to press CTRL+Shift+Enter when you enter the formula

you know it has worked when { } appear around the formula
like

Please Login or Register  to view this content.

tried this formula gives back whole number...not quite the same but easier to manage though you would need to understand how the formula works to mess around with it if you want to update sheet for whatever reason

Thanks a lot, i will work on trying to understand the structure, so i can update

David

it was all acer so dont forgot to * him


If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

What if I want to do this in Google sheet? what will be the formula? I have tried the formula below and it doesn't work in google sheet
{=IF(SUM(D4:J4)>C4,MATCH(TRUE,SUBTOTAL(9,OFFSET($D4:J4,,,,COLUMN($D4:J4)-COLUMN(D4)+1))>C4,0)-1,"MORE STOCKS")}