I made a (XY) Scatter chart in excel with two data series that almost align. I wish to add a trendline that encompasses both data series instead of just one.
How could one do this?
Thanks!
I never use chart trendline option, preferring to do my curve fitting using LINEST, but I don't think it has an option to fit a trendline to multiple data series. I would guess the easiest way would be to combine the two data series into one, and then fit the trendline to that.
I thought of that too but I need to have the series separate so they are differently shaped points
Does this link help at all?
http://support.microsoft.com/kb/114629
oldchippy
-------------
If you are happy with the help you have received, please click the blue scales icon in the blue bar of the post.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
Not really...
But you can't create a trendline based on two series. So be a little creative:Originally Posted by Kaatt
Series 1=series1
Series 2=series2
Series 3=combination of series 1 and 2: format as "invisible" (no line, no marker).
Trendline based on Series 3.
My personal preference, though, is still to use LINEST, based on the combination of series one and two, then series 3 = correlated values calculated in spreadsheet.
Thanks!!! I'll try that tomorrow!!!
Not knowing the LINEST function, personally, I used a little creativity to get a trendline based on both series of data:
Column A = Increments (1-10)
Column B = Data points Series 1
Column C = Data points Series 2
Add both sets of points to a chart. Add a trendline for both series, then format the trendlines to show their equations. You will get equations in the y=mx+b format, where M is the slope of each line, and b the y-intercept.
From this information, I averaged the two slopes to get a midpoint, then averaged the two y-intercepts to get the midpoint y-intercept.
In column D, I then put the formula "=AverageSlope*A1+AverageYIntercept" (where AverageSlope is the midpoint of the slopes mentioned earlier, and AverageYIntercept is the midpoint of the y-intercepts.) Copy down for rest of rows.
This essentially gives you a 3rd data series, which you can then plot on the chart. Edit the new series to add a trendline, then remove the data markers from that series so you're left with just the trendline. Don't forget to remove the trendlines and equations from the first two data series, too. That should do it.
I looked up LINEST on the Microsoft website and didn't feel like reading the many pages of its usage. Someday, maybe.
MrShorty,
I did not understand the process of merging two series explained by you. Could you give explanation on this please? I have the same problem of large number of tables with variable rows and need to get one trend line. I am following the option of pasting all the columns one after the other but this is a very tedious task. Please help.
Regards,
NS
Nothing magical. With the sizes of data sets I normally work with, manually copying and pasting would probably be adequate. Most of the time I probably would use formulas to link the cells. If series_1 is in columns A and B (row 1:100), I would put the formula =A1 and =B1 where I wanted to put the combined series and copy it down 100 rows. At the bottom of that, put a formula referencing the second series, and copy that down. As irritating as tedious is, sometimes I find it is the easiest and least aggravating.
If you want, I'm sure you could figure out VBA Sub procedure (macro) that would automate the process for you.
hi i have a scatter graph with a set of data i entered. i want to add separate trend lines to differant parts of the graph as there is a pronounced change in the curve. i dont want one overall trendline for the curve just two trend lines, one for each pronounced part of the graph. can this be done.
At the start of the graph the line is very steep and later on the slope of the line is a lot gentler.
Last edited by freezer502; 02-28-2007 at 12:52 PM.
So you want to do the opposite of the previous people. You need to split your 1 data series into 2 data series, split where you want the "change" of slope to be, and plot those two series with accompanying trendlines.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks