FORECAST and/or LINEST able to extrapolote Date if notavailable in detail?

fixit9660

New Member
Joined
May 2, 2014
Messages
10
Office Version
  1. 2010
Platform
  1. Windows
I'm recording fuel consumption and aim to predict when the fuel will run out.
As I am only recording the actual weekly dates of the readings, will FORECAST, and/or LINEST be able to predict the actual day/date that fuel expires, or just the two dates either side of the empty point please?
Oil Usage 2.xlsx
ABCDEFGHIJKLMNOP
1DateReading (inches)Volume Remaining (Litres)UsageNotes
210/10/202031.250793
312/10/202048.1251221#N/ATop up 465 Litres.
417/10/202047.000119329
524/10/202046.87511893
631/10/202045.500115435
707/11/202043.667110847
814/11/202041.837106246
921/11/202040.000101547
1028/11/202038.25097144
1105/12/202036.25092051
1212/12/202034.12586654
1319/12/202032.50082541
1426/12/202030.62577748
1502/01/202128.12571463
1609/01/202125.12563776
1716/01/202123.00058454
1823/01/202120.50052063
1930/01/202118.12546060
2006/02/202115.87540357
2113/02/202113.37533963
22
Sheet1
Cell Formulas
RangeFormula
E2:E21E2=IF(59*22*D2*0.0195478=0,NA(),59*22*D2*0.0195478)
F4:F21F4=IF(E3-E4=E3,NA(),E3-E4)
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I would suggest:

=INT(FORECAST(E22, $C$5:$C$21, $E$5:$E$21))

formatted as a date, where E22 contains the minimum amount (zero?).

But I would do my "homework" first (pun intended). ;) That would explain, for instance, why I would start with C5 and E5, excluding the first 3 data points.

-----

For the same "known Y" (column C) and "known X" (column E) data, FORECAST and LINEST use the same linear formula to estimate the best-fit straight line, namely y = x*m + b.

(Also, SLOPE returns m, and INTERCEPT returns b.)

We cannot use LINEST directly to extrapolate the data. But we might use the values returned by LINEST (or SLOPE and INTERCEPT) in a formula of the form =x*m + b, substituting appropriate cell references.

FORECAST is a simpler way to calculate the same result.

But in all cases, we assume that a straight-line fits the original data well, if not "the best".

We should confirm that assumption by charting the data first to determine which trendline fits the data best.

To that end, it would be prudent to use relative day numbers instead of actual dates, with 1 corresponding to the first date (10/10/2020). Thus, starting with B3, we might enter formulas of the form =C3-C2+B2 into column B.

The reason is: Excel dates are represented by integers, which are 5 digits these nowadays. For example, today (Feb 13 2021) is represented by 44240. Some trendlines raise that number to high powers, and we might be loose significant precision in the calculation.

With that in mind, the following is a chart of the data.

forecast trendline.jpg


The first 3 data points (in red) are far off the linear trendline that closely fits the remaining data (in blue).

As a practical matter, we should exclude the 1st data point (C2, E2) because it precedes the date when the fuel tank was last topped off.

I would also exclude the 2nd and 3rd data points (C3:C4, E3:E4), since they appear to be "outliers".

But arguably, if we include the 2nd and 3rd data points, the resulting trendline is not too much different. The choice is yours to make.

In any case, since a linear trendline does indeed fit the data well, we can use the dates in column C for the "known Y" values in formulas, instead of the relative day numbers.

And that justifies our use of FORECAST.


-----
Some unrelated observations....

1. Your formulas in column E and F can be simplified, to wit:

E2: =IF(D2=0, NA(), 59*22*D2*0.0195478)
F4: =IF(E4=0, NA(), E3-E4)


2. Is 0.0195478 correct for your purposes?

To convert cubic inches to liters, we should multiply by 0.016387064 = 25.4^3 * 1E-6.
 
Upvote 0
Here is another idea to consider. When you "top off", make a note that includes those words in the Notes column. The formula in column G "looks" back to find the last "top off" date and then uses FORECAST to estimate when the Volume Remaining will be 0...so this is a running estimate. Just pull the formulas down as more data are added to the table. As suggested by joeu2004, your existing formulas could benefit from some simplifying...I've offered one interpretation of that too...
MrExcel20210212.xlsx
ABCDEFGH
1DateReading (inches)Volume Remaining (Litres)UsageProjected Vol=0Notes ("top up")
210-Oct-2031.250792.9
312-Oct-2048.1251221.10.0 Top up 465 L
417-Oct-2047.0001192.528.513-May-2021
524-Oct-2046.8751189.43.210-Oct-2021
631-Oct-2045.5001154.534.9 Top up
77-Nov-2043.6671108.046.522-Apr-2021
814-Nov-2041.8371061.546.422-Apr-2021
921-Nov-2040.0001014.946.622-Apr-2021
1028-Nov-2038.250970.544.424-Apr-2021
115-Dec-2036.250919.850.722-Apr-2021
1212-Dec-2034.125865.953.918-Apr-2021
1319-Dec-2032.500824.641.219-Apr-2021
1426-Dec-2030.625777.047.619-Apr-2021
152-Jan-2128.125713.663.416-Apr-2021
169-Jan-2125.125637.576.111-Apr-2021
1716-Jan-2123.000583.653.98-Apr-2021
1823-Jan-2120.500520.163.45-Apr-2021
1930-Jan-2118.125459.960.33-Apr-2021
206-Feb-2115.875402.857.11-Apr-2021
2113-Feb-2113.375339.463.431-Mar-2021
fixit9660
Cell Formulas
RangeFormula
E2:E21E2=59*22*D2*0.0195478
F3:F21F3=MAX(E2-E3,0)
G3:G21G3=IFERROR(FORECAST(0, OFFSET($C$2, AGGREGATE(14,6,(ROW(H$2:H3)-ROW(H$1))/ISNUMBER(SEARCH("top up",H$2:H3)),1) - MAX(ROW($C$1)), 0, ROWS(G$1:G3) - AGGREGATE(14,6,(ROW(H$2:H3)-ROW(H$1))/ISNUMBER(SEARCH("top up",H$2:H3)),1) - MAX(ROW($C$1))+1, 1), OFFSET($C$2, AGGREGATE(14,6,(ROW(H$2:H3)-ROW(H$1))/ISNUMBER(SEARCH("top up",H$2:H3)),1) - MAX(ROW($C$1)), 2, ROWS(G$1:G3) - AGGREGATE(14,6,(ROW(H$2:H3)-ROW(H$1))/ISNUMBER(SEARCH("top up",H$2:H3)),1) - MAX(ROW($C$1))+1, 1) ),"")
 
Upvote 0
I misfired on the typing...use the words "top up" not "top off"
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top