4

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?

Improve this question

2 Answers 2

Reset to default
3

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
Improve this answer
3
  • 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?
    – Dumbo
    Jun 13, 2013 at 13:21
  • Did you add the = at the beginning? Excel needs that to understand you're entering a formula.
    – dav
    Jun 13, 2013 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, 2013 at 13:37
0

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.

Improve this answer
7
  • 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, 2013 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, 2013 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, 2013 at 0:11
  • 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, 2013 at 0:32
  • @chuff Thanks mate, I tried to dolow your step but I cant get something similar you got...I select two coloumns of R,P values and time one...but it just doesnt work with column chart
    – Dumbo
    Jun 13, 2013 at 11:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .