2

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?

2

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"))))
2
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.