# 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

• 19.2 KB Views: 5

#### vletm

##### Excel Ninja
Helen_Joan
One sample ...
... with cell E7 could set number of same rate days.
E-column would show ... when ( and F-column number of days) .

#### Attachments

• 26.3 KB Views: 6

#### 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

• 30.9 KB Views: 6

#### 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
Helen_Joan
One sample ...
... with cell E7 could set number of same rate days.
E-column would show ... when ( and F-column number of days) .
This was how I was trying to do it, but then got caught on the non equal intervals. Thank-you!

#### 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

#### p45cal

##### Well-Known Member
I have the 2016 version, so do not have the extra capability of 360. FORCAST.LINEAR
FORECAST will work in Excel 2016