Path: csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!npeer03.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!postnews.google.com!q2g2000vbv.googlegroups.com!not-for-mail From: Onion Knight Newsgroups: comp.os.linux.advocacy,microsoft.public.excel.programming,comp.apps.spreadsheets,microsoft.public.excel.misc,microsoft.public.excel Subject: Re: Correct process for creating a linear trend line in Excel Date: Mon, 18 Jun 2012 21:16:30 -0700 (PDT) Organization: http://groups.google.com Lines: 273 Message-ID: <67a99fdd-89ec-41b9-8db0-5b8927bf621b@q2g2000vbv.googlegroups.com> References: NNTP-Posting-Host: 89.248.172.227 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1340079835 11668 127.0.0.1 (19 Jun 2012 04:23:55 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Tue, 19 Jun 2012 04:23:55 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: q2g2000vbv.googlegroups.com; posting-host=89.248.172.227; posting-account=_0vpWgoAAABd8MtSaoNV-NkL_YvC-Yc_ User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows NT 6.1; rv:10.0) Gecko/20100101 Firefox/10.0,gzip(gfe) X-Received-Bytes: 12115 Xref: csiph.com comp.os.linux.advocacy:115093 comp.apps.spreadsheets:36 On Jun 18, 7:09=A0pm, Steve Carroll wrote: > On Jun 15, 9:03=A0pm, Onion Knight wrote: > > > > > > > > > > > On Jun 16, 12:45=A0am,Snit wrote: > > > > On 6/15/12 4:22 PM, in article jrgg4u$to...@dont-email.me, "joeu2004" > > > > wrote: > > > > "Onion Knight" 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 lin= e", which > > > > refers to yet-another "a debate in COLA". > > > > > No matter; I'm not interested. =A0I 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-cli= ck > > > method to get to some tools where the MS page suggests using the Ribb= on). > > > > > I see no missing steps in creating the linear trendline per se usin= g Excel. > > > > Thank you. =A0I think anyone who knows Excel well... or even not so w= ell... or > > > even poorly but can read the steps provided by MS... can see this. = =A0But > > > someone in COLA kept saying I was missing steps: > > > > =A0 cc: > > > =A0 =A0 ------ > > > =A0 =A0 I will gladly educate your ignorant ass, and give you step by > > > =A0 =A0 step instructions on how to correctly do it so you can see > > > =A0 =A0 all your missing steps, but first you have to stop lying. > > > =A0 =A0 ------ > > > =A0 =A0 So you are missing some key steps before doing the trendline. > > > =A0 =A0 I have said that many times now. > > > =A0 =A0 ------ > > > > He later said he was in reference to doing more than just creating th= e trend > > > line, which is all I said I was doing - he claimed I was missing step= s to do > > > part of a process I never claimed to do. =A0Yes: his claims were idio= tic. > > > 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 d= ata > > > 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: > > > > > > > And also able to show how the trend changed for the first half of 201= 2: > > > > > > > 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: > > > > > > > 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 opt= ion to > > > > Format Trendline Label in order to alter the display precision of t= he > > > > coefficients. =A0I would select Scientific with 14 decimal places i= f I want to > > > > see the trendline coefficients "exactly"; that is, the most precisi= on 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 creat= ed. > > > > Correct. =A0Nor do they remove the fact there are other forms of anal= ysis that > > > can be done. > > > > > FYI, we can also use the Excel function LINEST to determine the reg= ression > > > > line coefficients. > > > > > If the dates are in A1:A24 and the percentages are in B1:B24, selec= t two > > > > horizontal cells and array-enter the following formula (press > > > > ctrl+shift+Enter instead just Enter): =A0=3DLINEST(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 fo= rmat the > > > > LINEST results as Scientific with 14 decimal places. > > > > > You might notice infinitesimal differences between the trendline > > > > coefficients and the LINEST results. =A0But in this case, they are = same up 10 > > > > or 11 decimal places in this case; "close enough for government wor= k". =A0As a > > > > guess, the difference might be attributed to physically different i= nternal > > > > algorithms and/or to differences in internal floating-point arithme= tic > > > > anomalies. > > > > > For some insight into how the Excel LINEST and linear trendline coe= fficients > > > > might be derived, seehttp://en.wikipedia.org/wiki/Simple_linear_reg= ression. > > > > > Note that =3DCOVAR(A1:A24,B1:B24)/VARP(A1:A24) in E3 and > > > > =3DAVERAGE(B1:B24)-E3*AVERAGE(A1:A24) are about the same as the LIN= EST > > > > results. =A0The difference is in the 3 least-significant binary bit= s of the > > > > floating-point representations in this case. > > > > > You might also take note of the wiki page section titled "Normality > > > > assumption". =A0That may or may not be relevant to the COLA debates= . > > > > Not really... though the idea of a normal distribution was also debat= ed. =A0A > > > bit of info you might or might not know: you can easily visualize whe= re the > > > standard deviation (sigma) lines should be drawn on a normal distribu= tion... > > > the lines are *always* at the distance from the mean to the inflectio= n point > > > on the curve (where the concavity changes). =A0I was repeatedly told = by the > > > same person the distance was irrelevant: > > > > =A0 cc: > > > =A0 =A0 ----- > > > =A0 =A0 There'se nothing wrong with the image, other than some weird > > > =A0 =A0 axis labeling. > > > =A0 =A0 ----- > > > =A0 =A0Snit'sso fucking stupid he thinks the sigma lines are drawn > > > =A0 =A0 based on distance from the mean, not area under the curve. > > > =A0 =A0 ----- > > > =A0 =A0 | The sigma lines are drawn based on the area of the curve - > > > =A0 =A0 | which is easy to see when the images screw it up, esp. when > > > =A0 =A0 | they do so really badly, like in some of the ones I showed > > > =A0 =A0 | you. > > > =A0 =A0 They are not wrong. > > > =A0 =A0 ------ > > > =A0 =A0 LOL!!!! All of those links are fine. The first sigma lines > > > =A0 =A0 cover 68% of the area UNDER THE CURVE. > > > =A0 =A0 ----- > > > =A0 =A0 If you would like to prove, on any single one of the links > > > =A0 =A0 you call incorrect, that the first sigma lines do not bound > > > =A0 =A0 an area that is 68.2% of the area UNDER THE CURVE, then I > > > =A0 =A0 would like to see it. > > > =A0 =A0 ----- > > > =A0 =A0 Hahahaha your "approximate inflection points" are hilarious. > > > =A0 =A0 Please, post more on this subject. > > > =A0 =A0 ------ > > > > I explained to him why he was wrong here: > > > > There are many examples of people who should know better getting this= wrong. > > > Here are some > > > > From: > > > > > > > Which is the example I used for showing how him can make a decent > > > approximation visually: . > > > > 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): > > > > From= : > > > > > > Sigma lines clearly not at a far enough distance from the mean, esp. = on the > > > graph to the right. > > > > From: > > > > > > 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 bett= er. =A0I > > > openly admit I am not a math wiz but it is rather silly when sites cl= aiming > > > 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). =A0:) > > > > Anyway, sorry to have the idiotic debate spread to other forums... th= ough it > > > is sorta fun to see people who clearly know what they are talking abo= ut rip > > > apart those who were calling me names and insisting they "knew" I was= wrong. > > > :) > > > > -- > > > The indisputable facts about that absurd debate: > > > cc being proved wrong about his stats BS: > > > 7 simple questions cc will *never* answer: > > > cc again pretends to be knowledgeable about things he is clueless abo= ut. > > > 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.