Singletrack Mountain Bike Magazine

• Overview
• Bike Forum
• Chat Forum
• For Sale
• Wanted
• Forum Help
• Classified Rules

Chat Forum

1. Kit - Member

   Hello, I need to get a graph working properly, but Excel seems to do it's hardest to wind me up when it comes to making graphs, so here I am seeking your collective help

   Excel 2003.

   X-axis: Number of days 0-35
   Y-axis: pH 4-7

   So far so good, however my problem is that pH is not measured every day. So it will be measured days 0-7, 14, 21, 28, 35. I want a line graph to "join the dots", but Excel doesn't want to do that seemingly because there's no data on the inbetween days... I don't want a best fit/extrapolated R^2 type curve, simply lines between each data point.

   Does this make sense? If so, how do I sort it? HELP! Thanks

   Posted 1 year ago #
2. poly - Member

   I think I understand what you want, and can guess where your problem is.

   Whilst you think you want a "line" graph, actually you want an x-y scatter graph (this will cope with irregular spaced data, missing values etc).

   So when you choose the type of graph in the wizard (e.g. bar graph, pie chart, line plot) ignore the line plot even though it LOOKS like what you want. Pick x-y scatter. Then select the one with dots and lines joining them. Should be fixed.

   If that isn't the issue then post more info - or a table of your data and I will send it back in xls.

   Posted 1 year ago #
3. ScotlandTheScared - Member

   I am guessing (as I dont have Excel any more as I use linux) but I imagine you are selecting to plot a 'line' graph. If you instead tell it to plot a 'scatter' graph you can tell it to join the dots by selecting the correct style of scatter graph.

   Try it - and apologies if I am wrong.

   *damn it - beaten by a few seconds!*

   Posted 1 year ago #
4. Kit - Member

   Day pH
   0 6.8
   1 6.8
   2 6.4
   3 6.3
   4 6.2
   5 5.8
   6 5.5
   7 5.2
   8
   9
   10
   11
   12
   13
   14 4.8
   15
   16
   17
   18
   19
   20
   21 4.7
   22
   23
   24
   25
   26
   27
   28 4.7
   29
   30
   31
   32
   33
   34
   35 4.6

   Plotted as "Scatter with data points connected by lines", it only joins up days 0-7 and leaves the rest as unconnected points.
   edit: formatting is obviously not right, but it should be obvious!

   Posted 1 year ago #
5. Rob Hilton - Member

   You can do it using a line graph if you use a formula to force an error in the empty cells.

   e.g. In cell B10 (etc.) have:
   =VLOOKUP(A10,C10,1,0)

   where this is nonsense and will return #N/A

   If the data set for the chart is contiguous Excel will overlook these errors and plot the chart, lines & all, as you want.

   Posted 1 year ago #
6. Kit - Member

   Ah cheers Rob, cunning and it works!

   Posted 1 year ago #
7. Rob Hilton - Member

   I'm a cunning geek

   Posted 1 year ago #
8. poly - Member

   Rob - there's no need for your complicated vlookup formula to fix this, you are still using a line plot - which is actually not really ideal (it assumes that the data are evenly spaced, and will prevent you using any of the trend lines if they are ever requried etc). So its better that kit understands that for plotting pairs of scientific data a line plot is not the best way.

   Kit - the simplest option is just to delete the blank rows. I am guessing you've probably only included them to fix the spacing between the points in the line plot anyway! If you delete the blank rows and plot with an x-y scatter plot your data will plot as you wish.

   if those rows need to be there for are reason (e.g. there is actually another column with say temperature that is recorded every day) then I am 99% sure that there is a setting in excel as to how the plot treats blank data. Like ScotlandTheScared I don't routinely use excel either (also being a Linux convert) but will check how you get to this setting on another machine and get back to you...

   OK - a quick google suggests (section 9.7 of this: http://www.google.co.uk/url?sa=t&source=web&cd=7&ved=0CFIQFjAG&url=http%3A%2F%2Fwww.dur.ac.uk%2Fresources%2Fits%2Finfo%2Fguides%2F34Excel2003Charts.pdf&rct=j&q=excel%20ignore%20missing%20data%20chart%20xy%20scatter&ei=qtrXTZTEKpOt8QOb4_GDBQ&usg=AFQjCNGBUb2Jplndc9txM-YnMzFrBgvhqQ) that you need to Select the chart, go to Options, tools, chart tab - then select "plot empty cells as 'interpolated'"

   Posted 1 year ago #
9. Rob Hilton - Member

   Complicated? My arse.

   Posted 1 year ago #
10. Kit - Member

    Poly, thanks for that. Yup the X-Y scatter works without the blank cells, which as you said was what I was trying to achieve but with a line graph. And that .pdf is great too, thanks!

    Kit

    Posted 1 year ago #

RSS feed for this topic

Reply

You must log in to post.