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


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

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 (here). After that, you can post your question and our members will help you out.