Groups | Search | Server Info | Login | Register

Groups > comp.apps.spreadsheets > #36

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

Cross-posted to 5 groups.

Show all headers | View raw

On Jun 18, 7:09 pm, Steve Carroll <fretwiz...@gmail.com> wrote:
> On Jun 15, 9:03 pm, Onion Knight <onionknight...@gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> > On Jun 16, 12:45 am,Snit<use...@gallopinginsanity.com> wrote:
>
> > > On 6/15/12 4:22 PM, in article jrgg4u$to...@dont-email.me, "joeu2004"
>
> > > <joeu2...@foo.bar> 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.
>
> > > Really the video is all you need to see (I am the one who created it and the
> > > other related images with trend lines)... it shows the steps and they match
> > > what MS suggests exactly (with the exception that I use the right-click
> > > method to get to some tools where the MS page suggests using the Ribbon).
>
> > > > I see no missing steps in creating the linear trendline per se using Excel.
>
> > > Thank you.  I think anyone who knows Excel well... or even not so well... or
> > > even poorly but can read the steps provided by MS... can see this.  But
> > > someone in COLA kept saying I was missing steps:
>
> > >   cc:
> > >     ------
> > >     I will gladly educate your ignorant ass, and give you step by
> > >     step instructions on how to correctly do it so you can see
> > >     all your missing steps, but first you have to stop lying.
> > >     ------
> > >     So you are missing some key steps before doing the trendline.
> > >     I have said that many times now.
> > >     ------
>
> > > He later said he was in reference to doing more than just creating the trend
> > > line, which is all I said I was doing - he claimed I was missing steps to do
> > > part of a process I never claimed to do.  Yes: his claims were idiotic.
> > > This is the nature of the "advocacy" groups.
>
> > > Bottom line: my steps for creating a linear trend line were flawless, which
> > > is not to say creating such a line was the only form of analysis that could
> > > be done on such data or that the creation of a linear trend line on data
> > > that is clearly not linear is the best way to do an analysis.
>
> > > Using the same steps, though, I was able to show a trend line with a very
> > > good fit for the second half of 2011:
> > > <http://tmp.gallopinginsanity.com/LinuxTrend2011-2ndhalf.png>
>
> > > And also able to show how the trend changed for the first half of 2012:
> > > <http://tmp.gallopinginsanity.com/LinuxTrendLine2012.jpg>
>
> > > The fact is, a linear trend line is not going to show a very good fit with
> > > non-linear data... or data where the trend changes.
>
> > > > I would also select the trendline options to display the trendline equation
> > > > and the R-squared of the trendline.
>
> > > You can see where I did that in the above links... and in others that I
> > > made... a whole bunch of them combined:
> > > <http://tmp.gallopinginsanity.com/LinuxMultLinearTrendLines.png>
>
> > > Even then my labeling is not done well... but my only claim was that I made
> > > the linear trend line correctly, not that the graph had good labeling, etc.
> > > I openly acknowledge it did not.
>
> > > > 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.
>
> > > Seems a bit excessive for the needs here - but I did know about those
> > > options.
>
> > > > But none of those "steps" affects how the linear trendline is created.
>
> > > Correct.  Nor do they remove the fact there are other forms of analysis that
> > > can be done.
>
> > > > 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).
>
> > > I have used this function a couple of times... but never for any real work.
> > > :)
>
> > > > 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, seehttp://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.
>
> > > Not really... though the idea of a normal distribution was also debated.  A
> > > bit of info you might or might not know: you can easily visualize where the
> > > standard deviation (sigma) lines should be drawn on a normal distribution...
> > > the lines are *always* at the distance from the mean to the inflection point
> > > on the curve (where the concavity changes).  I was repeatedly told by the
> > > same person the distance was irrelevant:
>
> > >   cc:
> > >     -----
> > >     There'se nothing wrong with the image, other than some weird
> > >     axis labeling.
> > >     -----
> > >    Snit'sso fucking stupid he thinks the sigma lines are drawn
> > >     based on distance from the mean, not area under the curve.
> > >     -----
> > >     | The sigma lines are drawn based on the area of the curve -
> > >     | which is easy to see when the images screw it up, esp. when
> > >     | they do so really badly, like in some of the ones I showed
> > >     | you.
> > >     They are not wrong.
> > >     ------
> > >     LOL!!!! All of those links are fine. The first sigma lines
> > >     cover 68% of the area UNDER THE CURVE.
> > >     -----
> > >     If you would like to prove, on any single one of the links
> > >     you call incorrect, that the first sigma lines do not bound
> > >     an area that is 68.2% of the area UNDER THE CURVE, then I
> > >     would like to see it.
> > >     -----
> > >     Hahahaha your "approximate inflection points" are hilarious.
> > >     Please, post more on this subject.
> > >     ------
>
> > > I explained to him why he was wrong here: <http://youtu.be/MoW3hMq-eIc>
>
> > > There are many examples of people who should know better getting this wrong.
> > > Here are some
>
> > > <http://www.udel.edu/htr/Statistics/Images/Class12/normal2.gif> From:
> > > <http://www.udel.edu/htr/Statistics/Notes/class12.html>
>
> > > Which is the example I used for showing how him can make a decent
> > > approximation visually: <http://tmp.gallopinginsanity.com/sd.png>.
>
> > > I also pointed to some other examples which at least appear incorrect to me
> > > (though they are not as far off as the above example):
>
> > > <http://www.footballguys.com/shickstandard_1_files/image009.gif> From:
> > > <http://www.footballguys.com/shickstandard_1.htm>
> > > Sigma lines clearly not at a far enough distance from the mean, esp. on the
> > > graph to the right.
>
> > > <http://www.gsseser.com/images/StandardDeviation2s.gif> From:
> > > <http://www.gsseser.com/Deviation.htm>
> > > Sigma lines clearly not at a far enough distance from the mean.
>
> > > You would think that such sites would be made by people who knew better.  I
> > > openly admit I am not a math wiz but it is rather silly when sites claiming
> > > to be teaching such things get their depictions wrong (of course, one of the
> > > sites above is from "Footballguys"... and you might not expect them, by
> > > stereotype, to be the most knowledgeable in such areas anyway).  :)
>
> > > Anyway, sorry to have the idiotic debate spread to other forums... though it
> > > is sorta fun to see people who clearly know what they are talking about rip
> > > apart those who were calling me names and insisting they "knew" I was wrong.
> > > :)
>
> > > --
> > > The indisputable facts about that absurd debate: <http://goo.gl/2337P>
> > > cc being proved wrong about his stats BS: <http://goo.gl/1aYrP>
> > > 7 simple questions cc will *never* answer: <http://goo.gl/cNBzu>
> > > cc again pretends to be knowledgeable about things he is clueless about.
>
> > Funny how while Steve is throwing a toddler tantrum
>
> The tantrum is allSnit's... here is what he was originally arguing
> before he realized his stepped on his d*ck:
>
> https://groups.google.com/group/comp.os.linux.advocacy/msg/edd65e9dce...

Funny how you keep trolling Snit but can't show where he was wrong in
any place he did not point out.

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