Oct 07 2020 04:26 AM
I'm using Excel to keep track of my weight, e. g.
Jan 1 90
Jan 2 91
Jan 3 92
The table grows continually on a daily basis, the full-blown table containing several thousand rows
to cover the last decade or so.
I generated a diagram for the data by marking it and selecting a line plot.
I would like the diagram to automatically include ALL the data in particular any new data which I append, of course, ignoring any empty cells at the end of the table.
How do I do this?
Also consider a function, e. g. =MAX(B3:B8000) assumming that 3 is first row or the table and the weight is in column B. Is there more elegant formula than this,i.e., not using the arbitrary and perhaps cryptic B8000.
=MAX(B3:B) doesn't work.
Oct 07 2020 04:53 AM
Excel allows you to define dynamic named ranges - see for example Dynamic Named Range.
In your example, you could create a range named XValues defined as
=OFFSET(Sheet1!$A$1,2,0,COUNTA(Sheet1!$A:$A)-2,1)
assuming that A1 and A2 are filled, but the dates begin in A3. Sheet1 is the name of the sheet with data.
Similarly, create a range named YValues defined as
=OFFSET(Sheet1!$B$1,2,0,COUNTA(Sheet1!$B:$B)-2,1)
You can now specify =Sheet1!XValues as the range for the category axis values of your chart, and =Sheet1!YValues as the range for the series.
The chart will dynamically adapt itself as you add data.
And you can use =MAX(YValues) to return the maximum weight.
Oct 07 2020 08:07 AM
Solution@Maxwellq Why not simply convert your data into a structured table (Ctrl-T) and run your graph off that table. Such tables expand automatically and anything referring to the table as a whole or a column that is part of the table will expand with it as demonstrated in the attached file. Just add a few lines of dates and weights at the bottom and see what happens.
Oct 09 2020 04:11 AM
Oct 09 2020 04:36 AM - edited Oct 09 2020 05:21 AM
@Maxwellq Uploading my original file again, with several formulae in column N, that pick the highest weight from column B. See which one suits you best.
Oct 07 2020 08:07 AM
Solution@Maxwellq Why not simply convert your data into a structured table (Ctrl-T) and run your graph off that table. Such tables expand automatically and anything referring to the table as a whole or a column that is part of the table will expand with it as demonstrated in the attached file. Just add a few lines of dates and weights at the bottom and see what happens.