Thursday, February 08, 2007

Any Spreadsheet Jockeys Out There ?

This should be easy. But it seems impossible in Excel. How do I do it ? My son has had to do the graphs by hand for his school project because Daddy can't sort it.

We've got a series of pressure readings, taken daily, and the corresponding temperatures. Looks kinda like this

1003 4
1003 5
1001 5
993 9
995 10
1008 4
1009 3
1009 2
1010 0
1010 1

a quick look tells us that lower temps seem to be associated with high pressure and high temps with low. We want to graph this, to produce an X-Y scatter chart where X is, say, 990 to 1010 and Y is 0 to 10.

Doesn't look too difficult. But Excel seems to insist on calling the first set of values a series - or something. Anyway, I can't get it to produce this simple graph. What am I missing ? Surely Excel can't be that bad ?

UPDATE - even with the helpful advice in the comments I can't get the beast to work. Doh !

8 comments:

Harry Hutton said...

Is this post supposed to be humorous?

Anonymous said...

Try this; create your incorrect chart, right click on the graph background (not on a grid line, you'll get a different menu) and on the drop down menu that appears choose "Source Data". Then click on the "Series" tab where you can give your graph a name and choose the X and Y values to plot.

Hope that is what you are looking for.

Paul

Anonymous said...

Shouldn't present too much of a problem. If the data is put into two separate Excel columns then the data is selected and the Chart wizard chosen you can select "X-Y Scatter" which is about the 5th selection down. Unless you have previously used the sort function (data -> sort)to re-order the data so that the first column is in numerical order then I suggest you just do a scatter chart with individual points (the first available choice). You have to select "series in columns".

I have just cut pasted your data into Excel and it worked fine. I put the data into the first two columns (A&B). Excel tells me that the series is "=Sheet1!$A$1:£B$10 and the series tab tells me that the x values are "=Sheet1$A$1:$A$10" and the Y values are "=Sheet$B$1:$B$10".

I sorted the data and then I could choose the option where the scatter chart values are joined by a smooth curve. I guess it would be preferable to calculate a best fit line in which case the LINEST function would do it (but I don't remember how to use it).

Harry Hutton said...

Laban, sorry man. Re-reading it, it kind of makes sense. I thought all those numbers might have been an in-joke about some TV show or John Prescott or something.

Anonymous said...

I've emailed you a copy of the graph I came up with. I simply typed in the values in columns A and B, highlighted them, clicked on the Chart Wizard and selected the X-Y Scatter Graph option.

Anonymous said...

Y is Pressure, X is Day

You can define Day as 1,2,3,

and then it should plot with an OLS to run through the points...

Grpah Paper was a much better experience

Anonymous said...

As someone else stated - your Y data fields are A and B since you are regressing two data points against each other on the Y axis with one data field (days) on the X axis

Anonymous said...

I don't use M$ excel but it seemed to work fine in http://www.openoffice.org/