• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting material empty date using previous consumption rates

Helen_Joan

New Member
Good morning everyone;

I am looking for a solution to figure out when the material in my silo will run out looking at each of the last 7 days consumption rates. (ie, if we use material at the same rate as the last 7 days, it will be empty on day dd/mm/yyyy, but if we use material at the same rate as the last 1 day, it will be empty on day dd/mm/yyyy).

One solution would be to plot my data on an X/Y chart with date being X, and then getting the trendline, finding out the equation, and then plug in 0 for my Y.
I would love to make this more automated as we have multiple silos.

Another problem I run into is that the silo measuring intervals are not consistent. They are usually read at midnight, 6 am and 6 pm, sometimes they are read at noon. Sometimes there is a glitch in the system and there is no reading. And then I need to account for times when the silo is filling with material.

I know this is not simple, (but maybe it is!) but from lurking on this forum I know there are many brilliant people out there! (am I laying it on too thick?) :)
 

Attachments

p45cal

Well-Known Member
In the attached compare cell D93 of the Date sheet with the trendline crossing the x-axis on the X-Y chart sheet.
The only differences between the formulae in columns D & E are two instances of 7 becoming 1.
If you don't have FORECAST.LINEAR in your version of Excel, FORECAST will do.
Do you have Office 365 version of Excel? If so are functions such as FILTER, SEQUENCE available to you?
The formula may need to be entered with Ctrl+Shift+Enter rather than plain Enter, depending on your version of Excel.
 

Attachments

Peter Bartholomew

Well-Known Member
This would be so much easier if you know how much material had been added over the interval. As it is I have to ignore any change that occurres when material is added because I can't tell how much had been removed. The estimated number of days to empty the silo is simply the material level divided by the removal rate,
Code:
= LET(
  n,   ROWS(weight),
  s,   XMATCH(MAX(date)-days, date),
  k,   SEQUENCE(n-s, 1, s),
  W₀,  INDEX(weight,k),
  W₁,  INDEX(weight,k+1),
  T₀,  INDEX(date,k),
  T₁,  INDEX(date,k+1),
  ΔW,  IF(W₁<W₀, W₀-W₁),
  ΔT,  IF(W₁<W₀, T₁-T₀),
  rate,SUM(ΔW)/SUM(ΔT),
  W,   INDEX(weight, n),
  W/rate )
 

Helen_Joan

New Member
In the attached compare cell D93 of the Date sheet with the trendline crossing the x-axis on the X-Y chart sheet.
The only differences between the formulae in columns D & E are two instances of 7 becoming 1.
If you don't have FORECAST.LINEAR in your version of Excel, FORECAST will do.
Do you have Office 365 version of Excel? If so are functions such as FILTER, SEQUENCE available to you?
The formula may need to be entered with Ctrl+Shift+Enter rather than plain Enter, depending on your version of Excel.
I have the 2016 version, so do not have the extra capability of 360. FORCAST.LINEAR I had not run across yet, but will now keep an eye out for other uses. Thank-you for taking the time! this will help :)
 
Top