Groups | Search | Server Info | Login | Register

Groups > comp.apps.spreadsheets > #19

Re: Correct process for creating a linear trend line in Excel

Cross-posted to 6 groups.

Show all headers | View raw

"Onion Knight" <onionknightgot@gmail.com> wrote:
> There has been some debate in COLA as to the correct way
> to create a linear trendline in Excel. Someone in the forum
> kindly made a video to show what he thought was the correct
> method
> http://tmp.gallopinginsanity.com/LinearTrendLineCreation.mov
[....]
> Does anyone see any missing steps for the creation of a
> linear trend line?

I cannot find the original debate in comp.os.linux.advocacy (COLA); just the 
thread titled "Visualizing where to draw the standard deviation line", which 
refers to yet-another "a debate in COLA".

No matter; I'm not interested.  I think we can answer your Excel questions 
without getting into anything so esoteric.

I see no missing steps in creating the linear trendline per se using Excel.

I would also select the trendline options to display the trendline equation 
and the R-squared of the trendline.

And after displaying the trendline equation, I might select the option to 
Format Trendline Label in order to alter the display precision of the 
coefficients.  I would select Scientific with 14 decimal places if I want to 
see the trendline coefficients "exactly"; that is, the most precision that 
Excel will display.

But none of those "steps" affects how the linear trendline is created.

FYI, we can also use the Excel function LINEST to determine the regression 
line coefficients.

If the dates are in A1:A24 and the percentages are in B1:B24, select two 
horizontal cells and array-enter the following formula (press 
ctrl+shift+Enter instead just Enter):  =LINEST(B1:B24,A1:A24).

To compare with the "exact" trendline coefficients, I would also format the 
LINEST results as Scientific with 14 decimal places.

You might notice infinitesimal differences between the trendline 
coefficients and the LINEST results.  But in this case, they are same up 10 
or 11 decimal places in this case; "close enough for government work".  As a 
guess, the difference might be attributed to physically different internal 
algorithms and/or to differences in internal floating-point arithmetic 
anomalies.

For some insight into how the Excel LINEST and linear trendline coefficients 
might be derived, see http://en.wikipedia.org/wiki/Simple_linear_regression.

Note that =COVAR(A1:A24,B1:B24)/VARP(A1:A24) in E3 and 
=AVERAGE(B1:B24)-E3*AVERAGE(A1:A24) are about the same as the LINEST 
results.  The difference is in the 3 least-significant binary bits of the 
floating-point representations in this case.

You might also take note of the wiki page section titled "Normality 
assumption".  That may or may not be relevant to the COLA debates.

Back to comp.apps.spreadsheets | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Correct process for creating a linear trend line in Excel Onion Knight <onionknightgot@gmail.com> - 2012-06-15 14:16 -0700
  Re: Correct process for creating a linear trend line in Excel Peter Köhlmann <peter-koehlmann@t-online.de> - 2012-06-16 00:29 +0200
    Re: Correct process for creating a linear trend line in Excel Onion Knight <onionknightgot@gmail.com> - 2012-06-15 20:01 -0700
      Re: Correct process for creating a linear trend line in Excel Snit <usenet@gallopinginsanity.com> - 2012-06-15 20:21 -0700
    Re: Correct process for creating a linear trend line in Excel Onion Knight <onionknightgot@gmail.com> - 2012-06-15 20:05 -0700
      Re: Correct process for creating a linear trend line in Excel Snit <usenet@gallopinginsanity.com> - 2012-06-15 21:12 -0700
  Re: Correct process for creating a linear trend line in Excel "joeu2004" <joeu2004@foo.bar> - 2012-06-15 16:22 -0700
    Re: Correct process for creating a linear trend line in Excel Snit <usenet@gallopinginsanity.com> - 2012-06-15 17:45 -0700
      Re: Correct process for creating a linear trend line in Excel Onion Knight <onionknightgot@gmail.com> - 2012-06-15 20:03 -0700
        Re: Correct process for creating a linear trend line in Excel Snit <usenet@gallopinginsanity.com> - 2012-06-15 21:11 -0700
        Re: Correct process for creating a linear trend line in Excel Steve Carroll <fretwizzer@gmail.com> - 2012-06-18 12:09 -0700
          Re: Correct process for creating a linear trend line in Excel Onion Knight <onionknightgot@gmail.com> - 2012-06-18 21:16 -0700
            Re: Correct process for creating a linear trend line in Excel Snit <usenet@gallopinginsanity.com> - 2012-06-18 23:03 -0700
              Re: Correct process for creating a linear trend line in Excel Steve Carroll <fretwizzer@gmail.com> - 2012-06-19 09:17 -0700
            Re: Correct process for creating a linear trend line in Excel Steve Carroll <fretwizzer@gmail.com> - 2012-06-19 11:09 -0700
    Re: Correct process for creating a linear trend line in Excel Steve Carroll <fretwizzer@gmail.com> - 2012-06-15 18:39 -0700
    Re: Correct process for creating a linear trend line in Excel Onion Knight <onionknightgot@gmail.com> - 2012-06-15 19:54 -0700
      Re: Correct process for creating a linear trend line in Excel Snit <usenet@gallopinginsanity.com> - 2012-06-15 21:06 -0700
    Re: Correct process for creating a linear trend line in Excel Martin Brown <|||newspam|||@nezumi.demon.co.uk> - 2012-06-17 08:57 +0100
      Re: Correct process for creating a linear trend line in Excel Steve Carroll <fretwizzer@gmail.com> - 2012-06-17 09:18 -0700
        Re: Correct process for creating a linear trend line in Excel Onion Knight <onionknightgot@gmail.com> - 2012-06-17 10:23 -0700
          Re: Correct process for creating a linear trend line in Excel Steve Carroll <fretwizzer@gmail.com> - 2012-06-17 13:02 -0700
          Re: Correct process for creating a linear trend line in Excel Snit <usenet@gallopinginsanity.com> - 2012-06-17 15:09 -0700
    Re: Correct process for creating a linear trend line in Excel Onion Knight <onionknightgot@gmail.com> - 2012-06-18 21:33 -0700
      Re: Correct process for creating a linear trend line in Excel Snit <usenet@gallopinginsanity.com> - 2012-06-18 23:19 -0700
  Re: Correct process for creating a linear trend line in Excel Onion Knight <onionknightgot@gmail.com> - 2012-06-18 21:29 -0700
    Re: Correct process for creating a linear trend line in Excel Snit <usenet@gallopinginsanity.com> - 2012-06-18 23:16 -0700

csiph-web