1

I am developing a "Daily Personal Inventory" spreadsheet based off a related Lifehacker article.

I've successfully made a few modifications based on my own circumstances/goals, but there is one particular change that I am having trouble with.

I would like my scores to reflect only the most recent 30 entries (i.e., the last 30 form responses).

How can I reference a cell range relative to the bottom of the sheet rather than the top?

|improve this question
1

Not familiar exactly with the way this particular sheet works but I would try using a =sumif and passing the timestamp. See here.

|improve this answer
  • Oh, that actually might be more useful than the approach I was thinking of, as I could then explicitly select all values from the last month rather than kludging it by trying to select the last 30 rows. – user22405 Jul 21 '12 at 2:35
  • 3
    If you use the "=large(F:F,30)" where F is the column with your timestamps, that will tell you the 30th largest value, then just sum everything with a timestamp greater than that. – mhoran_psprep Jul 21 '12 at 4:02
0

For completeness, I'll outline some approaches to the problem as stated. Specifically for form responses, the comment by mhoran_psprep offers a neat solution: 

=FILTER(A2:Z, A2:A>=LARGE(A2:A,30))

filters the data (columns A-Z here) so that only the last 30 rows are shown. However, it may be necessary to guard against there being fewer than 30 rows, when Large throws an error. This could be done with IFERROR: 

=FILTER(A2:B, A2:A>=IFERROR(LARGE(A2:A,30), MIN(A2:A)))

Here is an alternative way, which does not rely on the values being sorted (but assumes no blanks between data). Adapted from Stack Overflow answer by dohmoose): 

=FILTER(A2:A, ROW(A2:A) > COUNTA(A1:A)-30)

To also support blanks cells in between, there are multiple approaches, none of which is clearly better than others. I refer to Selecting the last value of a column and Last non-empty cell in a column - Google Docs for comparison.

|improve this answer

Your Answer

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