Google-sheets – Find slope/intercept of last 30 values entered

google sheets

I have a column A7:A that has numbers from 1+;

I have another column B7:B, that has dates from Oct 31st, 2015 +;

I have a column F7:F that has weight values;

I have a goal weight of 108.36297798;

I need to find on what date from today will I reach my goal weight, but I only want to count values from the last 30 days on and not everything. This means that some dates may be empty but I want last 30 available values to be counted, so if it's empty go back until there's 30 values.

So far I've tried;

=DATEVALUE(TODAY() + ((108.36297798)-(INTERCEPT(F7:F,A7:A)))/(SLOPE(F7:F,A7:A)))

this just returns all the values, I want only last 30?

Best Answer

The command 

=query(A7:F, "select A, F where F is not null order by A desc limit 30")

returns the last 30 values where F is nonempty. If this command is placed, say, in G1, then it fills the array G1:H30, to which you can then refer: 

=DATEVALUE(TODAY() + ((108.36297798)-(INTERCEPT(H1:H30, G1:G30)))/(SLOPE(H1:H30, G1:G30)))

In principle, one can avoid creating a helper array and have just one formula returning the result. This requires replacing the references to A with 

=query(A7:F, "select A where F is not null order by A desc limit 30")

and the references to F with 

=query(A7:F, "select F where F is not null order by A desc limit 30")

The result is kind of long and scary but it works. 

=DATEVALUE(TODAY() + ((108.36297798)-(INTERCEPT(query(A7:F, "select F where F is not null order by A desc limit 30"), query(A7:F, "select A where F is not null order by A desc limit 30"))))/(SLOPE(query(A7:F, "select F where F is not null order by A desc limit 30"), query(A7:F, "select A where F is not null order by A desc limit 30"))))
Related Topic