Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a set of data as follow:

enter image description here

As you can see the first column has either R or P, the second column is a measured value, and third one is obviously time.

I need to present this in a chart (I believe it is called sample points versus time). Here is an example chart similar to what I need to have:

enter image description here

In my case I need to only draw sample points that are marked as R and the P data should not be shown in the chart. Is it possible to do this using excel? If not, can you give me a suggestion?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Another method is to use Excel's Scatter (XY) Chart. ONe major advantage of the Scatter Chart over a Column (or other chart type), is that it will plot your points at their exact location along the X Axis, without worrying about gaps or redundancies in your axis data.

  1. Add an extra column to your data for the Y Axis values. Here's what the data looks like: Sample Data 5
    • Note that the formula is slightly different. The 0.5 value was how I created the double row in the last sample. The basic would have the NA() as mentioned in step 2.
    • To fill down, just drag the cell's handle (bottom right corner, dark square) down your column and it'll fill the values. You can also double click the "handle' and it'll autofill down the column as far as you have data in the adjacent column (column C). Or (and this is usually my preference) you could convert your existing data into a Table, and once you type your first formula in the next column, Excel will autofill the rest of the column for you.
  2. Use a formula like =IF(A2="R",NA(),1) to give all "R" points a value of 1, and the "P" points an NA() value (which won't plot points in Excel).
  3. Insert a new, blank Scatter (with only markers) Chart.
  4. Right click the chart to Select Data.
  5. Add a Series, with the following values:
    • Series Name =A1
    • X Values =C2:C100 (or whatever your range is).
    • Y Values =D2:D100 (or whatever your range is).
  6. At this point your chart should look like this: Sample Points 1
    • To get the appropriate look as your example, we'll need to tweak this.
  7. Select your data set and add Error Bars Layout>Analysis>Error Bars>More Error Bar Options. This will open the Format Error Bars dialog box-make sure that it's set for Vertical Error Bars.
  8. Format the Error Bars, with the following values:
    • Display Direction = Minus
    • End Style = No Cap
    • Error Amount = Fixed Value = 1 (or 0.9 if you'd like a small white space between the bars and X Axis).
  9. Select the Horizontal Error Bars and delete them. Now your chart should look like this: Sample Points 2
    • The rest is just formatting.
  10. Here's a brief list of format changes, but most of this is personal preference:
    • Set both Axis limits and major/minor units.
    • Set your series Markers to none
    • Set your error bars line properties to whatever you prefer.
    • Adjust or remove titles and legends.
  11. Here's an example of what it could look like (with about 100 data points). Sampe Points 3
  12. This chart type is extremely flexible. For example, if you wanted to plot R and P points against each other, here's what it would look like: Sample Points 4
share|improve this answer
    
Thanks...but for putting formula I follwed your guid, but all the rows in Yaxis column have same formula now as if(A2="R",NA(),1) and I think I have to manually change A2 to A3..A4 for other rows? –  Sean87 Jun 13 '13 at 13:21
    
Did you add the = at the beginning? Excel needs that to understand you're entering a formula. –  dav Jun 13 '13 at 13:25
    
Also, the A2 reference is relative, so Excel should automatically increment the row numbers as you fill down (and the column letters if you were to fill right). You shouldn't have to automatically update any of those. –  dav Jun 13 '13 at 13:37

Yes, this is possible - though I'm not sure how Excel will react to the number of data points you have!

All that's really needed is a column chart. The X variable is time, set at equally spaced intervals. The Y variable will be set to 1 for the data points of interest (the R values in your example) and blank otherwise.

To produce the data for your chart, I would add a column to the right of the time values. For sake of example, assume that the first data cell (with the value "R") is in cell A1. Then, in cell D1, I would put the formula `=IF(A1="R",1,"") and copy the formula to the bottom of the data.

After creating the bare chart, then will be a bit of formatting work to be done:

  • setting the series overlap and gap width on the columns to 0%

  • removing column (bar) borders

  • removing the value labels on the X axis (set a custom number format of ";;;"),

  • Set the Y axis to set a maximum value of 1

  • removing any gridlines that have been automatically added

  • put in chart and axis titles

  • etc.

Here is a rough-around-the-edges example of what might be produced. I started with 1,000 data points with values of 1 and then eliminated the 1's at random to produce the gaps in the chart you see.

example sample point chart

This answer is admittedly schematic. If you need more step-by-step explanation, or clarification on some points, please let me know.

share|improve this answer
    
I interpreted the X's to elapsed time (in whatever units are applicable). The Y values are either 1 or nothing. What I was referring to in this bullet was the chart wizard extending the Y axis beyond the value of the largest Y data point. In this case, it shows a maximum value of 1.2, lowering the maximum value to 1 eliminates blank space. (On thinking it over, "rescaling" was probably not the right word to use.) –  chuff Jun 12 '13 at 23:59
    
I just actually looked at the fourth bullet point - of course! it's supposed to refer to the Y axis. Sorry for confusion. Y axis has fixed minimum of 0 and fixed maximum of 1. –  chuff Jun 13 '13 at 0:05
    
And for the X axis, I only changed the interval for tick marks and axis labels to a higher value (100, I think). –  chuff Jun 13 '13 at 0:11
    
I think there may be a problem with the X axis because (i) the data points are not uniformly spaced in time (though I think your sample assumed that) and (2) xl2007 does not offer units of less than a day for time. It also seems possible that the data may span more than a day. Otherwise I fully agree with the likes of your formula etc. –  pnuts Jun 13 '13 at 0:16
1  
If the data points are not uniformly spaced in time, then it would be necessary to construct such a series from minimum time to maximum, then do a lookup on the sample data to set the data points within an evenly-spaced time series. I'm trying to remember the details of 2007 - as I recall, time was the decimal portion of a date/time value, but I don't remember if it could represent less than a second. You're right, though, if the intervals are irregular (or have multiple data points), further processing would be needed. Probably would do on elapsed time, rather than actual time. –  chuff Jun 13 '13 at 0:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.