Groups | Search | Server Info | Login | Register

Groups > comp.apps.spreadsheets > #31

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

Cross-posted to 4 groups.

Show all headers | View raw

On Jun 17, 1:57 am, Martin Brown <|||newspam...@nezumi.demon.co.uk>
wrote:
> On 16/06/2012 00:22, joeu2004 wrote:
>
>
>
>
>
>
>
>
>
> > "Onion Knight" <onionknight...@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.
>
> The difference is that (apart from in some very early versions of XL2007
> where the graph regression function was wrecked to make it agree with
> another well known products answers) the linear regression on XL charts
> uses a well designed algorithm that gives a very good approximation to
> the true least squares parameter fit to data. LINEST is demonstrably
> numerically unstable although it requires at least a 3rd order
> polynomial fit and moderately hostile data to break it. Examples have
> been posted here or in another m.p.e group previously.
> (some years back)
>
> For linear fits the two are more or less interchangable but for higher
> orders of polynomial the chart function fit is more accurate.
>
>
>
> > 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.
>
> What is COLA?

The newsgroup where the troll Snit and his sock puppet Onion Knight
are lying and trolling with this topic.

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