best fit line

[jeremy via OfficeKB.com]

I have a line graph with 6 series plotted (I.e. 6 different lines--sales on y,
months of x). How do i add a best fit line to the chart? When i select Add
Treanline, it only lets you select one series (and I'm assuming the result
you get is a linear y=mx+b line....i 'm looking for the best fit line for
serveral series...
Thanks.
jeremy

 

[Guest]

since it is a line chart, there are several things you could try.
If it is justified, you could generate an average of the data points for
each x point and plot it. Make a trend line of the average then select the
average line and set line and symbol to none.

This is seldom accurate enough,
so you could also make a combined series of the data with the X location
duplicated as necessary. Do a slope and intercept calc on this data, Then
plot the data as your trend line.

 

[Jerry W. Lewis]

Assuming that when you created the chart you selected "Line" from the
Chart Type menu, you may get unexpected results. The "Line" chart is
misleadingly named; it has nothing to do with whether you want lines or
not, rather it treats the x-axis information (if any) as non-numeric
categories rather than numbers. If you try to fit a trendline to a
"Line" chart, the x values will be assumed to be 1,2,3,... regardless of
what x data (if any) was supplied.

Most users who want to fit a trend to their data want an "XY (Scatter)"
chart instead of a "Line" chart. You can change the chart type by
selecting the chart, and on the main menu choose Chart|Chart Type.

For trendlines, you are not limited to a linear fit. Follow the menu
and check out the options. You can put a different trendline on each of
the different data series. Your description is unclear on this point,
if all 6 series are sales (such as for different sales people) and you
interest is a combined trendline, then add a 7th super series that
combines the 6 series appropriately and use it to generate the
trendline. If appropriate, you could then format the 7th series with no
line type and no plot symbol, so that it wouldn't show on the graph.

Jerry

 

[jeremy via OfficeKB.com]

Jerry

Your last paragraph is what I'm interested in:

"You can put a different trendline on each of
the different data series. Your description is unclear on this point,
if all 6 series are sales (such as for different sales people) and you
interest is a combined trendline, then add a 7th super series that
combines the 6 series appropriately and use it to generate the
trendline. If appropriate, you could then format the 7th series with no
line type and no plot symbol, so that it wouldn't show on the graph."

I need a best fit line as a 7th series (is best fit the same as average? if
it is then how do i ad standard deviation bars to an average line?)
Thanks

jeremy

 

[Jerry W. Lewis]

Best fit is not the same as the average. If the 6 series share the same
x-values, you could average at each x value and plot that average
series, but there is no guarantee that those averages would exactly fit
a straight line, a polynomial, or whatever kind of trendline you might
want to fit. The trendline determines what function of the form that
you specify best fits (comes closest in the least squares sense) the data.

Jerry

 

[jeremy via OfficeKB.com]

Jerry,

my stats knowledge is slipping away.

I have 6 series--each is sales per month of different years, so each line
looks like a roller coaster (goes up and down) but it looks there are some
trends thru out the year. How would i summarize the data in one "trendline"
if I don't use average. I understand best fit line (a line that minimizes
the sum of (Y-y), but only have ever down that for linear relsationships
(y=mx+b)....

How is this done for (i'm not sure what this type of line is called) this
type of line/data?
(Stats 101 is the extent of my stats knowledge).

Thanks

Jeremy