Linear Trendline #2

Excel has created a linear trendline with its equations from the following 
points:
X	Y
1	4
3	12
6	15

the equations are:
y = 2.1053x + 3.3158
R2 = 0.8681

I wonder if you can tell me how excel create this linear trendline from the 
points shown.

Thanks in advance.
Maperalia

0
maperalia (28)
5/4/2006 2:03:01 AM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
834 Views

Similar Articles

[PageSpeed] 13

Maperalia  -

Excel uses "ordinary least squares," i.e., matrix operations that yield 
coefficients that minimize the sum of squared deviations.

For the simple linear case, the results are equivalent to the formulas in 
Excel's Help for the INTERCEPT and SLOPE worksheet functions.

See Excel's Help for the LINEST function for more details.

For more discussion of numerical accuracy, see
"Description of the LINEST function in Excel 2003 and in Excel 2004 for Mac" 
at
support.microsoft.com/kb/828533
and
"Description of improvements in the statistical functions in Excel 2003 and 
in Excel 2004 for Mac" at
support.microsoft.com/default.aspx?kbid=828888&product=xl2003

-  Mike
www.mikemiddleton.com

"maperalia" <maperalia@discussions.microsoft.com> wrote in message 
news:C945651B-EDD8-4E72-8C15-E23513F0CAD1@microsoft.com...
> Excel has created a linear trendline with its equations from the following
> points:
> X Y
> 1 4
> 3 12
> 6 15
>
> the equations are:
> y = 2.1053x + 3.3158
> R2 = 0.8681
>
> I wonder if you can tell me how excel create this linear trendline from 
> the
> points shown.
>
> Thanks in advance.
> Maperalia
> 


0
mike5208 (300)
5/4/2006 2:44:15 AM
Mike;
Thanks for your quick reponse.
I have read the information you adviced me. This information shows how the 
formulas to create a linear trend from this points
However, what I am looking is the formulas where excel takes these point and 
adjust them to make a linear trendline.

Wher do you think I can get this information?

Thanks in advance.
Maperalia

"Mike Middleton" wrote:

> Maperalia  -
> 
> Excel uses "ordinary least squares," i.e., matrix operations that yield 
> coefficients that minimize the sum of squared deviations.
> 
> For the simple linear case, the results are equivalent to the formulas in 
> Excel's Help for the INTERCEPT and SLOPE worksheet functions.
> 
> See Excel's Help for the LINEST function for more details.
> 
> For more discussion of numerical accuracy, see
> "Description of the LINEST function in Excel 2003 and in Excel 2004 for Mac" 
> at
> support.microsoft.com/kb/828533
> and
> "Description of improvements in the statistical functions in Excel 2003 and 
> in Excel 2004 for Mac" at
> support.microsoft.com/default.aspx?kbid=828888&product=xl2003
> 
> -  Mike
> www.mikemiddleton.com
> 
> "maperalia" <maperalia@discussions.microsoft.com> wrote in message 
> news:C945651B-EDD8-4E72-8C15-E23513F0CAD1@microsoft.com...
> > Excel has created a linear trendline with its equations from the following
> > points:
> > X Y
> > 1 4
> > 3 12
> > 6 15
> >
> > the equations are:
> > y = 2.1053x + 3.3158
> > R2 = 0.8681
> >
> > I wonder if you can tell me how excel create this linear trendline from 
> > the
> > points shown.
> >
> > Thanks in advance.
> > Maperalia
> > 
> 
> 
> 
0
maperalia (28)
5/4/2006 5:23:01 PM
Maperalia  -

I don't understand what other "formulas" you are looking for.

For example, the XY data is used by the INTERCEPT and SLOPE functions (or by 
the chart trendline feature), and the fitted Y values for the line are 
determined using =INTERCEPT+SLOPE *X.

What more do you want?

-  Mike

"maperalia" <maperalia@discussions.microsoft.com> wrote in message 
news:52A494CE-7BE0-4F38-A186-ECAD86E5E76D@microsoft.com...
> Mike;
> Thanks for your quick reponse.
> I have read the information you adviced me. This information shows how the
> formulas to create a linear trend from this points
> However, what I am looking is the formulas where excel takes these point 
> and
> adjust them to make a linear trendline.
>
> Wher do you think I can get this information?
>
> Thanks in advance.
> Maperalia
>
> "Mike Middleton" wrote:
>
>> Maperalia  -
>>
>> Excel uses "ordinary least squares," i.e., matrix operations that yield
>> coefficients that minimize the sum of squared deviations.
>>
>> For the simple linear case, the results are equivalent to the formulas in
>> Excel's Help for the INTERCEPT and SLOPE worksheet functions.
>>
>> See Excel's Help for the LINEST function for more details.
>>
>> For more discussion of numerical accuracy, see
>> "Description of the LINEST function in Excel 2003 and in Excel 2004 for 
>> Mac"
>> at
>> support.microsoft.com/kb/828533
>> and
>> "Description of improvements in the statistical functions in Excel 2003 
>> and
>> in Excel 2004 for Mac" at
>> support.microsoft.com/default.aspx?kbid=828888&product=xl2003
>>
>> -  Mike
>> www.mikemiddleton.com
>>
>> "maperalia" <maperalia@discussions.microsoft.com> wrote in message
>> news:C945651B-EDD8-4E72-8C15-E23513F0CAD1@microsoft.com...
>> > Excel has created a linear trendline with its equations from the 
>> > following
>> > points:
>> > X Y
>> > 1 4
>> > 3 12
>> > 6 15
>> >
>> > the equations are:
>> > y = 2.1053x + 3.3158
>> > R2 = 0.8681
>> >
>> > I wonder if you can tell me how excel create this linear trendline from
>> > the
>> > points shown.
>> >
>> > Thanks in advance.
>> > Maperalia
>> >
>>
>>
>> 


0
mike5208 (300)
5/4/2006 5:40:00 PM
Mike wrote  on Thu, 4 May 2006 10:40:00 -0700:

 MM> I don't understand what other "formulas" you are looking
 MM> for.

 MM> For example, the XY data is used by the INTERCEPT and SLOPE
 MM> functions (or by the chart trendline feature), and the
 MM> fitted Y values for the line are determined using
 MM> =INTERCEPT+SLOPE *X.

 MM> What more do you want?

 MM> -  Mike

 MM> "maperalia" <maperalia@discussions.microsoft.com> wrote in
 MM> message 
news:52A494CE-7BE0-4F38-A186-ECAD86E5E76D@microsoft.com...
 ??>> Mike;
 ??>> Thanks for your quick reponse.
 ??>> I have read the information you adviced me. This
 ??>> information shows how the formulas to create a linear
 ??>> trend from this points However, what I am looking is the
 ??>> formulas where excel takes these point and adjust them to
 ??>> make a linear trendline.
 ??>>  ??>> Wher do you think I can get this information?

I woder if the OP wants the actual details of calculating the 
slope and intercept? The equations are given in HELP, AFAIK.

James Silverton. 

0
5/4/2006 6:33:31 PM
Mike;
Thanks you very much. I really appreciatte your supporting in this matter.

Kind regards.
Maperalia

"Mike Middleton" wrote:

> Maperalia  -
> 
> I don't understand what other "formulas" you are looking for.
> 
> For example, the XY data is used by the INTERCEPT and SLOPE functions (or by 
> the chart trendline feature), and the fitted Y values for the line are 
> determined using =INTERCEPT+SLOPE *X.
> 
> What more do you want?
> 
> -  Mike
> 
> "maperalia" <maperalia@discussions.microsoft.com> wrote in message 
> news:52A494CE-7BE0-4F38-A186-ECAD86E5E76D@microsoft.com...
> > Mike;
> > Thanks for your quick reponse.
> > I have read the information you adviced me. This information shows how the
> > formulas to create a linear trend from this points
> > However, what I am looking is the formulas where excel takes these point 
> > and
> > adjust them to make a linear trendline.
> >
> > Wher do you think I can get this information?
> >
> > Thanks in advance.
> > Maperalia
> >
> > "Mike Middleton" wrote:
> >
> >> Maperalia  -
> >>
> >> Excel uses "ordinary least squares," i.e., matrix operations that yield
> >> coefficients that minimize the sum of squared deviations.
> >>
> >> For the simple linear case, the results are equivalent to the formulas in
> >> Excel's Help for the INTERCEPT and SLOPE worksheet functions.
> >>
> >> See Excel's Help for the LINEST function for more details.
> >>
> >> For more discussion of numerical accuracy, see
> >> "Description of the LINEST function in Excel 2003 and in Excel 2004 for 
> >> Mac"
> >> at
> >> support.microsoft.com/kb/828533
> >> and
> >> "Description of improvements in the statistical functions in Excel 2003 
> >> and
> >> in Excel 2004 for Mac" at
> >> support.microsoft.com/default.aspx?kbid=828888&product=xl2003
> >>
> >> -  Mike
> >> www.mikemiddleton.com
> >>
> >> "maperalia" <maperalia@discussions.microsoft.com> wrote in message
> >> news:C945651B-EDD8-4E72-8C15-E23513F0CAD1@microsoft.com...
> >> > Excel has created a linear trendline with its equations from the 
> >> > following
> >> > points:
> >> > X Y
> >> > 1 4
> >> > 3 12
> >> > 6 15
> >> >
> >> > the equations are:
> >> > y = 2.1053x + 3.3158
> >> > R2 = 0.8681
> >> >
> >> > I wonder if you can tell me how excel create this linear trendline from
> >> > the
> >> > points shown.
> >> >
> >> > Thanks in advance.
> >> > Maperalia
> >> >
> >>
> >>
> >> 
> 
> 
> 
0
maperalia (28)
5/4/2006 10:23:02 PM
Reply:

Similar Artilces:

Validation list question #2
I have created 2 validation lists in two different cells - D & E. Cell D contains the values 10A, 10B, and 10C. In cell E, the values are 1234, 1235, 1236. The cells are related in a sense that the first value in cell D goes with the first value in cell E, and so forth. My question is: when I select any of the values in D, how do I get the value to automatically input in E? Thanks in advance, Newtechie Hi newtechie assuming that you have your validations list in a separate range (lets say on a separate sheet called 'val_list') and they are ordered like the following A ...

Is it possible to generate a chart from a formula. or: Can I define and plot my own Trendline?
I'd like to generate a circle on a chart by entering the radius of the circle into a cell. Thanks in advance for any help, Greg Suppose the radius is in cell A1. Then, in B1 enter the formula =ROW()-1. In C1 enter =B1/180*PI(), in D1, =$A$1*COS(C1), and in E1 =$A$1*SIN(C1). Copy row 1 to 2:361. Plot D1:E361 in a XY Scatter chart. Once done, adjust the chart so that the x and y axis have the same min/max values. Also, select the plot area and extend it as close to the chart area as possible. Finally, select any cell. Then, hold down SHIFT and select the chart. Now, select...

Linear Programming
In Excel how do you set up a 3 variable linear programming problem on a spreadsheet with a a number of constaints using Solver? Franklin - See the tutorials and examples at www.solver.com. Or, search your drive for the file solvsamp.xls, which is installed when Solver is installed and which has a 3-variable nonlinear example that is easily converted to linear. - Mike www.mikemiddleton.com +++++++++++++++++++ "Franklin" <anonymous@discussions.microsoft.com> wrote in message news:0c5a01c500bf$8f5038c0$a501280a@phx.gbl... > In Excel how do you set up a 3 variable ...

Trendline coefficients
I have fit a series of data using a 5th order polynomial from the trendline dialog. Using the coefficients displayed in the equation, the curve is not faithfully reconstituted. Is it possible to export the trendline coefficients to arbitrary significant figures? Select the trendline equation on the greaph and format the number of significant figures "MrUniverseman" wrote: > I have fit a series of data using a 5th order polynomial from the trendline > dialog. Using the coefficients displayed in the equation, the curve is not > faithfully reconstituted. Is it po...

1 account, 2 computers and 2 email addresses
Good evening, I've got one broadband email account on two networked computers, both using Outlook 2000. I want each computer to receive their own respective emails, however, both computers are receiving emails from both accounts. I just double checked Outlook on this computer and under Accounts, only the account I created for this computer is there. Why am I still getting the other emails? And how do I filter so that each computer gets it's own respective mail? Thanks. Frank Phrank <Phrank> wrote: > I've got one broadband email account on two networked computers, ...

Division 2 numbers
Hello I want generate at random numbers in 2 columns that results division was be only integer example: 6/2=3 or 4/2=2 or 9/3=3 i will be do that in visual basic thx for help gregor -- E-mail odczytasz po kliknieciu na: http://cerbermail.com/?Sa8ZdOqkxK Nr gadu-gadu: 36786 One way: Option Explicit Sub testme() Dim myRng As Range Dim wks As Worksheet Dim MaxNumber As Long Dim MinNumber As Long Dim maxResult As Long Dim minResult As Long MaxNumber = 25 MinNumber = 3 maxResult = 10 minResult = 2 Set wks = ActiveSheet With wks ...

linear and exponential graphs and x,y intercepts
I have two lines one linear (y=mx+c) and one exponential (y=c^bx) and they intercept each other. I believe it is possible to calculate where the lines intercept. Although I can do this by graphing the data, it takes time and allows for user error so a calculation would speed things along greatly. The problem: So for the first line using the equation y=mx+c the numbers for the 13kPa line are y=0.0021x+9e-16 And for the exponential line (y=c^bx) which we produce to using the myograph is y=0.0022^0.0046x (^=Exp) I know that the two lines intercept at 1613. But no matter what I try I can&...

Public folders in OWA #2
I had a problem earlier this week with getting OWA to work, finally got it working, when i added the defualt app under IIS. But when it comes up now if you go to public folders it shows it as a Directory rather than the normal outlook contacts look....you can browse it all the way to the contact, like this: Wednesday, October 05, 2005 8:51 AM 893 Bob Ponder.EML Any ideas? Sounds like you modified something in IIS Admin, then had those settings propagate down to the Public vdir. The only way I've ever been able to fix the view problem you are mentioning is to reinstall...

GWAT #2
Can anyone help me with this question please? What is the GWAT in 5.5 called in 2003 and why is it better? Thanks in advance GWART = Gateway Address Routing Table. Exchange 200x uses link state routing instead of a GWART. This article will help: http://support.microsoft.com/?id=263249 -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchangeblog.com "Slipper1" <cunn1l1ngus@hotmail.com> wrote in message news:Pjegd.84$T34.48@fe2.news.blueyonder.co.uk... > Can anyone help me with this question please? > > What is the GWAT in 5...

If Statment #2
I need help with a formula that would do the following: for example i you has a list of leters in B1:B10 and a list of numbers for A1:A1 what I need to know is the fomula that would look at the letters i B1:B10 and if they met a value that I needed then would count th numbers in A1:A10. So, if B1:B10 = A then it would count the numbe value for A in A1:A10. Hope that made sense, if not I will try t clerify, thanks for the hel -- Message posted from http://www.ExcelForum.com Hi try =INDEX(A1:A10,MATCH("A",B1:B10,0)) -- Regards Frank Kabel Frankfurt, Germany > I need help with ...

Price List #2
I created a profile for an Inventory person, when she goes into price list and clicks on 'currency id' it says she does not have enough priviliges. Does she have access to the 'Currencies' window? "rcr" wrote: > I created a profile for an Inventory person, when she goes into price list > and clicks on 'currency id' it says she does not have enough priviliges. > > ...

How do I add new autostyles to outlines, more than RowLevel_1/2 (.
How do I add new autostyles to outlines, more than RowLevel_1/2 (like RowLevel_3 for 3rd outline level) ...

CFont #2
Hi, Here's a simple question. Here's a really small code snippet: *********************************** LOGFONT logfont; logfont.lfHeight = 20; logfont.lfWidth = 1; logfont.lfEscapement = 0; logfont.lfOrientation = 0; logfont.lfWeight = FW_NORMAL; logfont.lfItalic = FALSE; logfont.lfUnderline = FALSE; logfont.lfStrikeOut = FALSE; logfont.lfCharSet = ANSI_CHARSET; logfont.lfOutPrecision = OUT_DEFAULT_PRECIS; logfont.lfClipPrecision = CLIP_DEFAULT_PRECIS; logfont.lfQuality = DEFAULT_QUALITY; logfont.lfPitchAndFamily = DEFAULT_PITCH; strcpy(logfont.lfFaceName,"ALGERIAN"); CFo...

Multiple Calenders in Outlook Today #2
I am playing with having multiple calendar folders (e.g., personal and business) so that I can print the personal calender with family events and hang it on my icebox. Works fine except that Outlook Today only shows events from a single calendar. Is there any way that OL Today can merge the events from more than one calendar? ...

Final Results from 1 or 2 scores
i have made a report that shows the scores and placings for contestants. Some events have 2 sets of scores and so have two final results which get added togeter and then divided by 2 to give a final percentage in the final mark Box. Then sometimes the same event has only 1 set of scores and 1 result and so the report needs to reflect result 1 as the Final percentage eg Place Contestants name Result 1 Result2 Final mark -- 1 Betty Boo 56% 75% 65.5% 1 Betty Boo 56% ...

displaying comments #2
Is there any way to force comments to appear where I want them to appear instead of where Excel thinks they should appear. I want them displayed only when the cursor moves over them. Excel seems to always display comments to the right of the cell, but if the cell is on the right side of the spreadsheet, displaying it to the right may put half of it beyond the visible screen to the right. How can I force comments to display on the left of the cell so the users won't need to scroll right to read the comment? Thanks, Harold There is no way to force cell comments to appear anywher...

Saving #2
I accidently pressed the save button on an excel document when I actually wanted to save the file under a different name. How do I retreive the original document (unsave)? "kim" <anonymous@discussions.microsoft.com> wrote in message news:075901c3b3d8$27c585f0$a401280a@phx.gbl... > I accidently pressed the save button on an excel document > when I actually wanted to save the file under a different > name. How do I retreive the original document (unsave)? Sorry, you can't. The original has been overwritten. Of course, if you have a backup... ...

Venn Diagram #2
How do I add a label to a Venn diagram? In article <02219198-46B3-4D58-B093-FA172AC77228@microsoft.com>, Racheldt@discussions.microsoft.com says... > How do I add a label to a Venn diagram? > If you created this through Insert | Diagram... I suspect your choices are limited to what that component allows. Unfortunately, I have almost no experience with it. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions ...

Command Button #2
When you scroll down the page is there anyway the command button can scroll down as well. Thanks Mark You can kind of do it, but there are easier ways. I like to put those buttons in Row 1. Select A2 and hit Window|freeze panes. Row 1 will always be visible. Another way would be to make a toolbar that can be stuck where the user likes (floating or docked to the top/bottom/right/left). Mark wrote: > > When you scroll down the page is there anyway the command > button can scroll down as well. > > Thanks > > Mark -- Dave Peterson this worked a treat >-----...

CRM Sales for Outlook 1.2
Hi! We are using mscrm 1.2 successfully for a while now (no mail router, no outlook client). Now we tried to integrate it into our exchange environment using the outlook client. Online is working fine but the offline functionality isn't working. Obviously the local msde- database is not getting any data. The error message is: "Metadata-Cache error, No entity!" The log says: MSCRM Platform Error Report: ---------------------------------------------------------- ---------------------------------------------- Error: Exception from HRESULT: 0x80040E00. Error Message: Exception...

Signature picker greyed out #2
I am trying to add a signature using Outlook 2000. When I go to Tools > Options > Mail Format, the Signature Picker button is greyed out. Why is this not an option? ...

how do I graph a linear equation?
I want to graph the linear equation y = 2x + 4 in microsoft excel. How do I do this? On my TI-83 Plus I could easily do this. I also know I could create a data table and use the table of values to chart the line in excel. But I want just enter the equation in excel and have excel graph it. Thanks. On Sun, 9 Jan 2005, in microsoft.public.excel.charting, Throstle <Throstle@discussions.microsoft.com> said: >I want to graph the linear equation y = 2x + 4 in microsoft excel. How do I >do this? On my TI-83 Plus I could easily do this. I also know I could create >a data table and ...

SKU011.CAB #2
Dear Sir/Madam, I have a problem relating to the aforesaid file (SKU011.CAB). A few searches for solutions on Google has lead me to deduce that problems of this kind are extremely commonplace and difficult to resolve in the circumstances but I wanted to post on here anyway in the hope that someone might be able to help me. I am using Microsoft Office 2003 and practically everything on it seems to be working properly except for Excel. It appears to be trying to complete some sort of installation at the start and demands an original source file, SKU011.CAB, in order to do this. As Offic...

E-Newsletter #2
I am trying to send an e-newsletter embedded into the message. When I preview the publication in the browser it looks perfect although when I try to send the document it appears in the same format as when I am working on it- i.e not in HTML. Help! -- zoew ...

Why does 62 KB translate into 4.2 MB in Publisher?
When I added a 62 KB jpeg to my page, the overall file size jumped from ~275 KB to 4.2 MB. What gives? Let me guess - Publisher 2000? -- JoAnn Paules MVP Microsoft [Publisher] "Publisher induced baldness" <Publisher induced baldness@discussions.microsoft.com> wrote in message news:9ED85165-A29E-44AA-AFE6-4F5A5D518991@microsoft.com... > When I added a 62 KB jpeg to my page, the overall file size jumped from > ~275 > KB to 4.2 MB. What gives? Publisher 2000 doesn't support compression. PUB2000: File Size Increases Unexpectedly When You Insert Picture ...