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 !
Well, Of Course We Will, Emma…
19 hours ago
8 comments:
Is this post supposed to be humorous?
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
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).
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.
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.
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
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
I don't use M$ excel but it seemed to work fine in http://www.openoffice.org/
Post a Comment