N

#### NY

-----------------

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.