# Find slope/intercept of last 30 values entered?

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?

## 1 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"))))
``````
• A and B go on forever, meanwhile F only have values that I put in everyday. meaning the dates and numbers in B and A respectively are all there, so when you do descending limit, it actually calculates it on the last date no? It's not corresponding the numbers in A with numbers in F? It's also returning results in the past for some reason. – Spongebob Squarepants Feb 23 '16 at 15:50
• The query selects only the rows "where F is not null", and only then orders and limits to 30. If you get some rows you didn't expect, look at the content of F column in those rows. – user79865 Feb 23 '16 at 16:06