Creating a dynamic chart range where the range to be graphed depends on the contents of a cell




I have a spreadsheet with many rows of data, each with a date and time and
the values of various numerical parameters read at these times:

01/01/14 00:00 1.1 2.2
01/01/14 00:10 1.1 2.2
01/01/14 00:20 1.1 2.2
01/01/14 00:30 1.1 2.2
01/01/14 00:40 1.1 2.2
01/01/14 00:50 1.1 2.2
01/01/14 01:00 1.1 2.2
01/01/14 01:10 1.1 2.2
02/01/14 00:00 1.1 2.2
02/01/14 00:10 1.1 2.2

Hopefully the number of readings per day is the same every day - in this
case, 6 readings/hour * 24 hours = 144 readings - though I suppose I ought
to cope with the case where a reading is lost.

I want to plot a graph of one of the values (eg the third column) versus
time for a chosen date, with the date (or at least a simple integer
representing the day number) entered into a spreadsheet cell.

For one specific hard-coded day I can create an XY scatter graph with


In general, the start and end rows for any day number are:


where Offset is 2 (to account for the first row being column headings) and
RowsPerDay is 144 (6*24) - the simplest case where there are always the
same number of readings per day.


So my question is: how do I use these calculated start/end row values in the
formula for the chart?

I want the formula to be of the form


so that when I change the DayNumber cell, I get a graph for a different
day's data.


It would be nice to be able to devise a formula which says "extract from all
the data those rows whose column A matches a specified date and plot an XY
graph of X=column B and Y=column C for those rows", so as to cope with the
case where there are different numbers of rows for different days because of
missing data.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question