Hi,
I'm starting to chart health and fitness data and have set up charts that contain all data. I'd also like to create a couple of charts of key benchmarks covering the latest 7 days. I saw a post from 2011 that explained the concept to me (using a cell in the footer and referencing backwards by negative numbers???), but I don't get it. Any help is greatly appreciated!
Blessings,
Tim
MacBook Pro 13", 10.13
If you have data by date and you want to chart the latest 7 days only, then you could set up a separate table that extracts the latest 7 days of data from the table where you enter your data. Then you can base your chart on the separate table. It could look like this:
The formula in A2, filled down, is:
=LARGE(Data::A,9−ROW())
This lists the latest 7 dates.
The formula in B2, filled right and down, is:
=INDEX(Data::B,MATCH($A2,Data::$A,0))
This simply looks up the readings from the data table based on the date in column A.
Substitute ; for , in the formulas if you region uses , as a decimal separator.
The chart in the example is a 2D Scatter. You can adjust it to suit.
SG
If you have data by date and you want to chart the latest 7 days only, then you could set up a separate table that extracts the latest 7 days of data from the table where you enter your data. Then you can base your chart on the separate table. It could look like this:
The formula in A2, filled down, is:
=LARGE(Data::A,9−ROW())
This lists the latest 7 dates.
The formula in B2, filled right and down, is:
=INDEX(Data::B,MATCH($A2,Data::$A,0))
This simply looks up the readings from the data table based on the date in column A.
Substitute ; for , in the formulas if you region uses , as a decimal separator.
The chart in the example is a 2D Scatter. You can adjust it to suit.
SG
Are you looking for something like this?
The formula in B10:
=AVERAGE(OFFSET(B$10,−7,0,7,1))
The special form of offset starts with the base cell (in this example B1$10), counts back 7 rows (the -7), stays in the same column (the 0), then includes 7 rows (the 7), and includes only one column (the 1).
SG
I think that is what I’m looking for. Regardless of how many rows are added, I will always report the last 7?
Your example seems to return an average. If I want have data points from the last 7 days charted, how do I go about that?
Thank you so much!
NEVERMIND, I figured it out. Thanks for your help!
SG,
When I enter the formula, as written, it returns an #REF error. Do I need to include the name of the first Table?
Sorry, I'm totally green!
Blessings,
Tim
That looks exactly what I'm looking for! Thanks. I'll give it a try.
Charting latest 7 days
