Function to give me the EROD (Estimated Run Out Date)

[Excellover123]

Hi Message Board.
Just wondering if anyone can help me.
Is there a single cell formula that can take the qty of my stock & give me the EROD (Estimated Run Out Date) based on my demand.
I have tried the average function, however, as my demand is quite volatile between days this is often well out.
I have been resulting to having a cumulative volume off to the side & then a approximate look up to return when it can't meet the next qty.
As you can imagine, this is taking up loads of Excel real estate & is using far more functions than I'd ideally like.
Is there something better out there? I was thinking that a custom function might be the way to go?
Many thanks in advance
Gary

 

[James006]

Hi,
For a quick fix :

Formula to project inventory run out date

I'm trying to project the run out date of my inventory based off of last years history. I have data on sheet1 and sheet2. The first sheet is the Item# (columnA)and the Quantity on Hand (columnB). I want column C to tell me the date I would run out of inventory if it was reduced at the same...

www.mrexcel.com

 

[Excellover123]

Hi,
For a quick fix :

Formula to project inventory run out date

I'm trying to project the run out date of my inventory based off of last years history. I have data on sheet1 and sheet2. The first sheet is the Item# (columnA)and the Quantity on Hand (columnB). I want column C to tell me the date I would run out of inventory if it was reduced at the same...

www.mrexcel.com

Thank you, James. I will get that a go

 

[James006]

To make your life easier, my personal preference for your situation would be to go for a UDF ...

 

[Excellover123]

To make your life easier, my personal preference for your situation would be to go for a UDF ...

Yes, I agree mate