none
How can I build a graph with a time axis based on minutes:seconds, milli seconds (EXCEL2010)

    Question

  • I'm using Execl 2010 and I want to create a graph based on a time. The times solution has to be in ms

    time [mm:ss,ms]             data

    01:10,200                       27

    01:10,225                       130

    and so on.

    How can I format the x-axis to show such grapgh?

    michael

    Wednesday, December 12, 2012 8:08 AM

Answers

  • Create an XY scatter chart. This is the only chart type that supports a time scale on the x-axis.


    Regards, Hans Vogelaar

    Wednesday, December 12, 2012 8:58 AM
  • Time-series charts do not support for fractions of a day. The normal approach for time units of less than a day is to use a Scatter chart and set the Axis to display units of the relevant fraction of a day, eg for hours 1/24 or 0.04166667, for minutes 1/3600.

    However for your data, if(?) you have data for every 5 milli-seconds you could use an ordinary Line type. Split your times into two columns, h:mm in col-A (only one entry for each minute), milli-seconds in col-B, and finally the data in col-C. In X-Axis options select "Multi-level Category Labels" and go from there. When done, you should have two rows of ticklabels, milli-seconds in units of 5 over "centred" minutes.

    Peter Thornton

    Wednesday, December 12, 2012 10:17 AM
  • An XY chart is a Scatter chart in which both XY values are regarded as "data" values. In most other chart types the X values are "category" values. The difference might seem mute in certain scenarios but XY points can be plotted anywhere along the X-axis according to the X-value. However as "category" values points are plotted in order one after another.

    So for your purposes change the chart to a Line type as I suggested before, though that will only be appropriate if the time intervals are equally spaced and in logical order. Start like this -

    -Split the time values into two columns as I described before
    -Select the data values in the 3rd column and create a Line chart
    -Select the Line, click Select Data
    -In the right panel (Horizontal Category), Edit, select the two time columns, eg col-A & col-B
    -Right click the X-Axis, Format Axis, Axis Options, check "Multi-level Category Values" (though it will probably have been selected by default).

    Keep in mind you don't need to complete every cell in col-A, just one value for every significant change.

    After all that I'm not sure I thought through your original question. You want to plot every 5ms, right?, so that means 200 points per Second, not sure if I what I'm suggesting is practical. If not give a clear idea of the data set you are working with.

    Peter Thornton

    Wednesday, December 12, 2012 1:21 PM

All replies

  • Create an XY scatter chart. This is the only chart type that supports a time scale on the x-axis.


    Regards, Hans Vogelaar

    Wednesday, December 12, 2012 8:58 AM
  • Time-series charts do not support for fractions of a day. The normal approach for time units of less than a day is to use a Scatter chart and set the Axis to display units of the relevant fraction of a day, eg for hours 1/24 or 0.04166667, for minutes 1/3600.

    However for your data, if(?) you have data for every 5 milli-seconds you could use an ordinary Line type. Split your times into two columns, h:mm in col-A (only one entry for each minute), milli-seconds in col-B, and finally the data in col-C. In X-Axis options select "Multi-level Category Labels" and go from there. When done, you should have two rows of ticklabels, milli-seconds in units of 5 over "centred" minutes.

    Peter Thornton

    Wednesday, December 12, 2012 10:17 AM
  • Hello Peter,

    I'm using the xy-chart, is that right?

    where can i choose the mentioned "multi-level category label"?

    thanks michael

    Wednesday, December 12, 2012 11:03 AM
  • An XY chart is a Scatter chart in which both XY values are regarded as "data" values. In most other chart types the X values are "category" values. The difference might seem mute in certain scenarios but XY points can be plotted anywhere along the X-axis according to the X-value. However as "category" values points are plotted in order one after another.

    So for your purposes change the chart to a Line type as I suggested before, though that will only be appropriate if the time intervals are equally spaced and in logical order. Start like this -

    -Split the time values into two columns as I described before
    -Select the data values in the 3rd column and create a Line chart
    -Select the Line, click Select Data
    -In the right panel (Horizontal Category), Edit, select the two time columns, eg col-A & col-B
    -Right click the X-Axis, Format Axis, Axis Options, check "Multi-level Category Values" (though it will probably have been selected by default).

    Keep in mind you don't need to complete every cell in col-A, just one value for every significant change.

    After all that I'm not sure I thought through your original question. You want to plot every 5ms, right?, so that means 200 points per Second, not sure if I what I'm suggesting is practical. If not give a clear idea of the data set you are working with.

    Peter Thornton

    Wednesday, December 12, 2012 1:21 PM