N
NY
Background
-----------------
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
etc
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
formulae
X='Jan14log'!$B$2:$B$145
Y='Jan14log'!$C$2:$C$145
In general, the start and end rows for any day number are:
start_row=Offset+DayNumber*RowsPerDay
end_row=Offset+(DayNumber+1)*RowsPerDay-1
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.
Question
-------------
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
Y='Jan14log'!$C$start_row:$C$end_row
so that when I change the DayNumber cell, I get a graph for a different
day's data.
Refinement
----------------
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.
-----------------
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
etc
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
formulae
X='Jan14log'!$B$2:$B$145
Y='Jan14log'!$C$2:$C$145
In general, the start and end rows for any day number are:
start_row=Offset+DayNumber*RowsPerDay
end_row=Offset+(DayNumber+1)*RowsPerDay-1
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.
Question
-------------
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
Y='Jan14log'!$C$start_row:$C$end_row
so that when I change the DayNumber cell, I get a graph for a different
day's data.
Refinement
----------------
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.