Thread: Formula for date-based dynamic chart over unknown number of rows

I've been working on a spreadsheet for my personal finances. I have two complete years of transactions over four accounts. I wanted to graph the trends of each account and the sum of all accounts, but I only wanted to visualize the past year (365 days) of data.

I set out to create a dynamic chart and found a variety of resources online; one example was a dynamic chart for the past 12 months (12 entries) - all the examples I could find relied on a set number of data points. I have an unknown (or dynamic) number of daily transactions and the balance of my accounts after each such transaction. I managed to create a formula that gives me an offset based on the number of transactions over a set time period (365 days) rather than a set number of transactions:

Column A contains the date (YMD) of each transaction, column J contains the balance after the transaction.

Date:
=OFFSET($A1,COUNTA($A:$A)-COUNTIF($A:$A,">="&MAX($A:$A)-365),0,COUNTIF($A:$A,">="&MAX($A:$A)-365))

Balance:
=OFFSET($A1,COUNTA($A:$A)-COUNTIF($A:$A,">="&MAX($A:$A)-365),9,COUNTIF($A:$A,">="&MAX($A:$A)-365))

Those formulas are the same, save for the column offset "9" for the balance. For the row offset, I find the last row of data using COUNTA() (this is reliable because I'll never have a transaction entry without a date) and subtract from that the number of transactions (COUNTIF) that occurred later than 365 days prior to the latest transaction (MAX). I reuse the COUNTIF/MAX to create the range by specifying the OFFSET height parameter as the number of transactions later than 365 days prior to the latest transaction.

I created a named range for each formula, created my chart with the date and balance columns, then updated the data series using the named ranges for the X and Y values.

I now have a rolling chart for all transactions within the past year of my latest entry.

Hope someone else finds this useful!