Predict value from extrapolation, with a goal

[gcb1]

Suppose I have table with 2 colums. Date and a integer. I want to predict by which date the integer will get to zero based on current progression of known values.

date time	value
2024-01-13 22:00:00	601
2024-01-14 07:30:00	581
2024-01-16 12:00:00	486
2024-01-18 16:52:00	396
???	0

Even extrapolating the values is confusing calc. If i select those few rows and pull for many more or just two more… it will go back in time and raise the numbers, in a way that is not present on the data:

|2024-01-13 22:00:00|601|
|2024-01-14 07:30:00|581|
|2024-01-16 12:00:00|486|
|2024-01-18 16:52:00|396|
|2024-01-14 22:00:00|602|
|2024-01-15 07:30:00|582|
|2024-01-17 12:00:00|487|
|2024-01-19 16:52:00|397|
|2024-01-15 22:00:00|603|

[karolus]

Hallo
fortunatly its linear, so:

=-INTERCEPT(B$1:B$4;A$1:A$4)/SLOPE(B$1:B$4;A$1:A$4)

⇒ 2024-01-27 22:36:12

[Steph1]

A little bit of math here.
.
First, let’s suppose, and it is a big if, that the decrease over time is linear, or said differently, that the decrease is proportional to the time spent.
.
We know that between the first and last dates of the range, we changed by 396 - 601.
We want to know that between the first date and to-be-found date, we changed by 0 - 601.
Therefore, to-be-found date - first date = ((last date - first date) / (396 - 601)) * (0 - 601)
Hence, to-be-found date = first date + ((last date - first date) / (396 - 601)) * (0 - 601)

Replacing the hard coded values above by their cells, I get: 2024-01-27 22:45.

[gcb1]

great way to address this.

Meanwhile i was playing with something similar (but letting more of the math to Calc )

1st tried a delta time column (=(A3-A2))

then upgraded it to a delta in days (=DAYS(A3; A2)), which can then by brought to a delta hours (*24)

then just divide the delta value by the delta hours and you have the regression: =(B3-B2)/(DAYS(A3-A2)*24)

which then gives me a range of 1.7 ~ 2.1, which we can average to 2 and figure out the date simply by dividing the remaining value. ((396/2)/24 = 8.25, 2024-01-27 plus 1/4 )

but i’m still holding my breath for a linear regression function somwhere