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 🙂

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.

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!*

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!

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.

Ah cheers Rob, cunning and it works!

I’m a cunning geek 😕

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'”

Complicated? My arse.

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