none
Plotting datasets of different lengths in Excel 2007

    질문

  • Hi,

    I have an issue in Excel (2007) that I just can seem to solve satisfactorily. 

    I have two datasets of values recorded over the same time period, say 12:00 to 13:00.  One is averaged every second and the other is averaged every 5 minutes.  Therefore I have 2 datasets of different lengths but they are both over the same overall time period.  I know I can plot these sets of data in Matlab but can't figure out a way to do it in Excel.

    I want the 5 minute data as a bar (column) chart and the other can just be a line / scatter graph.

    Please help!

    Thanks.

    2012년 3월 14일 수요일 오후 11:58

답변

  • Hi,

    I have an issue in Excel (2007) that I just can seem to solve satisfactorily. 

    I have two datasets of values recorded over the same time period, say 12:00 to 13:00.  One is averaged every second and the other is averaged every 5 minutes.  Therefore I have 2 datasets of different lengths but they are both over the same overall time period.  I know I can plot these sets of data in Matlab but can't figure out a way to do it in Excel.

    I want the 5 minute data as a bar (column) chart and the other can just be a line / scatter graph.

    Please help!

    Thanks.

    Without getting into the details of why you have to simulate the result, here's how you can do what you want.

    Create a XY Scatter chart.  The time values go on the x-axis, whatever data you are measuring go on the y-values.

    For the 5 minute series, create a 100% negative error bar for the y values.  Change the marker so that it is not visible (no fill and no line).  Format the error series so that it is fairly thick, thick enough to look like a column.

    For the 1 minute series, change, if desired, the format to include a line.

    An alternative to the above that *should* work would be to create a column chart on the primary axis and a XY Scatter chart on the secondary axis.  Then, you'd have to format the 2 so that the axes are aligned as required.


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present

    2012년 3월 15일 목요일 오전 5:38