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

Discussion in 'Excel Charting' started by NY, Jan 30, 2014.

  1. NY

    NY Guest

    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.
     
    NY, Jan 30, 2014
    #1
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. eliese0
    Replies:
    2
    Views:
    332
    eliese0
    Aug 9, 2004
  2. BrianW
    Replies:
    4
    Views:
    215
    Jon Peltier
    Oct 22, 2004
  3. Simon
    Replies:
    4
    Views:
    154
  4. Gburg Johnny
    Replies:
    1
    Views:
    105
    Jon Peltier
    Sep 19, 2005
  5. Keith
    Replies:
    5
    Views:
    148
    Kelly O'Day
    Mar 13, 2006
  6. Kirsten
    Replies:
    3
    Views:
    174
    Jon Peltier
    Oct 10, 2006
  7. Aaron
    Replies:
    1
    Views:
    180
    Andy Pope
    Mar 27, 2008
  8. mellermj

    Adding data that is not graphed to chart

    mellermj, Mar 10, 2009, in forum: Excel Charting
    Replies:
    1
    Views:
    202
    Andy Pope
    Mar 10, 2009
Loading...