1

I have ascending dates in column A and percentages in column B. Here is an excerpt (last 5 rows):

       A                     B
 728   4/30/2018 9:24:09     36.80%
 732   4/30/2018 10:05:44    36.93%
 730   4/30/2018 10:52:41    36.99%
 731   4/30/2018 11:23:50    36.95%
 732   4/30/2018 12:42:06    36.75%

I want to plot a sparkline in cell C1 based only on percentages in column B that are from the last week. Edit: Please note that the last week should end at the last (most recent) date appearing in column A.

The end result should return a range between a specific B cell and up to the last non-empty B cell, to be used in the sparkline formula. How do I get this range?

Note: I imagine it should be something along the lines of =sparkline(x&:B), where x is the B cell that matches (same row) the A cell that has the date that's a week ealier than the date in the last populated A cell. But I'm not sure how to get this date, and how to write the sparkline formula.

Thanks!

David Spivak
  • 81
  • 9

1 Answers1

0
=sparkline(
  indirect("B"&match(today()-7, A:A, true)):
  indirect("B"&match(today(), A:A, true))
 )

enter image description here

ttarchala
  • 4,277
  • 2
  • 26
  • 36
  • Yes I have, posting screenshot now. – ttarchala Apr 30 '18 at 14:48
  • Great to hear that it works for you, it's customary to accept/upvote the answer :-) – ttarchala Apr 30 '18 at 14:56
  • The OP had specified that it's only the last 5 rows, it's easy to extrapolate how the rest of the data is expected to look like. – ttarchala Apr 30 '18 at 15:06
  • Thanks, works! Although I should have been more clear - when I said "the last week" I meant the last week up to the most recent date in column A (which doesn't necessarily match `today()`). To do this I replaced `today()` with `INDEX(FILTER(A:A,NOT(ISBLANK(A:A))),ROWS(FILTER(A:A,NOT(ISBLANK(A:A)))))` (based on [this question](https://stackoverflow.com/questions/8116043/get-the-last-non-empty-cell-in-a-column-in-google-sheets)). – David Spivak Apr 30 '18 at 15:08
  • 2
    @DavidSpivak That seems a quite complicated way of achieving the same as just MAX(A:A) :-) – ttarchala Apr 30 '18 at 15:10