Linear Regression

I have five numbers in:

A1,B1,C1,D1,E1

Is there a function that will give me the linear regression of those 
numbers that can be put in D1, with charting etc?

TIA
0
Saxman
2/23/2010 10:32:47 AM
excel 39879 articles. 2 followers. Follow

18 Replies
1249 Views

Similar Articles

[PageSpeed] 4

Linear regression works with ordered pairs.
row 1:   1,2,3,4,5
row 2:   2,4,6,8,<formula>
formula in E2 :  =FORECAST(E1,A2:D2,A1:D1) will return 10
Any use?
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Saxman" <john.h.williamsREMOVETHIS@btinternet.com> wrote in message 
news:XpidnXpInZMDMx7WnZ2dnUVZ8m1i4p2d@bt.com...
> I have five numbers in:
>
> A1,B1,C1,D1,E1
>
> Is there a function that will give me the linear regression of those 
> numbers that can be put in D1, with charting etc?
>
> TIA 

0
Bernard
2/23/2010 12:42:25 PM
On 23/02/2010 12:42, Bernard Liengme wrote:
> Linear regression works with ordered pairs.
> row 1: 1,2,3,4,5
> row 2: 2,4,6,8,<formula>
> formula in E2 : =FORECAST(E1,A2:D2,A1:D1) will return 10
> Any use?

Thanks.

Meant to say 'without' graphs.

Not sure how to set this up?

The values 0.8, 0.3, 1.7, 4.9, 6.7 against 1,2,3,4,5 when plotted in a 
graph give S=1.17, r=0.93.  R is the value I require.

Do I put the values 1,2,3,4,5 in A1:E1 and 0.8, 0.3, 1.7, 4.9, 6.7 in 
A2:E2?  However, you state that FORECAST goes in E2?

0
Saxman
2/23/2010 2:39:53 PM
There's a regression feature in the Analysis toolpack (which isn't, from 
what I remember, installed by default).

Other ways of doing regressions in excel are shown in Kardi Teknomo's 
website at

http://people.revoledu.com/kardi/tutorial/Regression/index.html



"Saxman" <john.h.williamsREMOVETHIS@btinternet.com> wrote in message 
news:XpidnXpInZMDMx7WnZ2dnUVZ8m1i4p2d@bt.com...
> I have five numbers in:
>
> A1,B1,C1,D1,E1
>
> Is there a function that will give me the linear regression of those 
> numbers that can be put in D1, with charting etc?
>
> TIA 

0
RobFJ
2/23/2010 5:14:35 PM
On 23/02/2010 17:14, RobFJ wrote:
> There's a regression feature in the Analysis toolpack (which isn't, from
> what I remember, installed by default).
>
> Other ways of doing regressions in excel are shown in Kardi Teknomo's
> website at
>
> http://people.revoledu.com/kardi/tutorial/Regression/index.html

Thanks for that.  I was aware and installed the feature.  However, I 
want to do some figures in batch mode.  There are videos on Youtube as well.
0
Saxman
2/23/2010 6:15:31 PM
 > The values 0.8, 0.3, 1.7, 4.9, 6.7 against 1,2,3,4,5 when plotted
 > graph give S=1.17, r=0.93. R is the value I require.

Hi.  Is 'S' (1.17) your Slope?

Don't know if this helps, but the linear slope is:

=SLOPE(A2:E2,A1:E1)
1.64

For 'R, see if INTERCEPT(), or RSQ() is what you need.

Dana DeLouis


On 2/23/2010 9:39 AM, Saxman wrote:
> On 23/02/2010 12:42, Bernard Liengme wrote:
>> Linear regression works with ordered pairs.
>> row 1: 1,2,3,4,5
>> row 2: 2,4,6,8,<formula>
>> formula in E2 : =FORECAST(E1,A2:D2,A1:D1) will return 10
>> Any use?
>
> Thanks.
>
> Meant to say 'without' graphs.
>
> Not sure how to set this up?
>
> The values 0.8, 0.3, 1.7, 4.9, 6.7 against 1,2,3,4,5 when plotted in a
> graph give S=1.17, r=0.93. R is the value I require.
>
> Do I put the values 1,2,3,4,5 in A1:E1 and 0.8, 0.3, 1.7, 4.9, 6.7 in
> A2:E2? However, you state that FORECAST goes in E2?
>


-- 
= = = = = = =
HTH  :>)
Dana DeLouis
0
Dana
2/23/2010 9:08:49 PM
On 23/02/2010 21:08, Dana DeLouis wrote:
>  > The values 0.8, 0.3, 1.7, 4.9, 6.7 against 1,2,3,4,5 when plotted
>  > graph give S=1.23, r=0.91. R is the value I require.

I have run this again and corrected the values for reference.

> Hi. Is 'S' (1.17) your Slope?

S=Standard Error
r=Correlation Coefficient

> Don't know if this helps, but the linear slope is:
>
> =SLOPE(A2:E2,A1:E1)
> 1.64
>
> For 'R, see if INTERCEPT(), or RSQ() is what you need.

Thanks Dana.

I got the FORECAST function to work (but different values). I will try 
this, as it looks like what I want.

I will report back.
0
Saxman
2/23/2010 10:35:12 PM
On 23/02/2010 22:35, Saxman wrote:

>> > The values 0.8, 0.3, 1.7, 4.9, 6.7 against 1,2,3,4,5 when plotted
>> > graph give S=1.23, r=0.91. R is the value I require.
>
> I have run this again and corrected the values for reference.

I tried
CORREL
PEARSON
RSQ
INTERCEPT

However, cannot appear to match the values above.

The funtion SPEARMAN might match, but not available with Excel.
0
Saxman
2/23/2010 10:59:58 PM
Excel 2007 Linest()
http://c0718892.cdn.cloudfiles.rackspacecloud.com/02_24_10.xlsx

0
Herbert
2/24/2010 5:36:57 PM
On 24/02/2010 17:36, Herbert Seidenberg wrote:
> Excel 2007 Linest()
> http://c0718892.cdn.cloudfiles.rackspacecloud.com/02_24_10.xlsx
>

That's fantastic!  Exactly what I wanted.

I'm much obliged to you.




0
Saxman
2/25/2010 3:08:55 PM
On Tue, 23 Feb 2010 10:32:47 +0000, Saxman
<john.h.williamsREMOVETHIS@btinternet.com> wrote:

>I have five numbers in:
>
>A1,B1,C1,D1,E1
>
>Is there a function that will give me the linear regression of those 
>numbers that can be put in D1, with charting etc?
>
>TIA


Why not just use the LINEST worksheet function?

For your series of:

0.8, 0.3, 1.7, 4.9, 6.7 

It returns the following array:

 1.64		-2.04
 0.372021505	 1.233855745
 0.866271579	 1.176435294
19.43352601	 3
26.896		 4.152

For some of what you've posted:

Slope	1.64
R2	0.87
Se	1.18

(Yes, for those values, using Linear Regression, R2 is 0.866272 or 0.87; not
0.93)

But all kinds of statistics are available from LINEST.  LINEST and the graph
use different algorithms, but I believe they pretty much agree starting with
Excel 2007 (and it was only certain types of data that had a problem pre-2007)

--ron
0
Ron
2/25/2010 8:01:56 PM
On 2/23/2010 4:08 PM, Dana DeLouis wrote:
> The values 0.8, 0.3, 1.7, 4.9, 6.7 against 1,2,3,4,5 when plotted in a
> graph give S=1.17, r=0.93. R is the value I require.

Hi.  I see you have an answer, but I was confused as to how you got 
these values from Excel's graph.  The best I can do is plot the linear 
equation, and display the R^2 value of 0.866. (I don't see any option to 
display Standard Error S = 1.17 on the graph)

Just to mention...

=SQRT(RSQ(y,x))
0.93

= = = = = = =
HTH  :>)
Dana DeLouis
0
Dana
2/25/2010 8:31:41 PM
On 25/02/2010 20:31, Dana DeLouis wrote:
>
> =SQRT(RSQ(y,x))
> 0.93
>
> = = = = = = =
> HTH :>)
> Dana DeLouis

Thanks
0
Saxman
2/26/2010 7:07:04 PM
On 24/02/2010 17:36, Herbert Seidenberg wrote:
> Excel 2007 Linest()
> http://c0718892.cdn.cloudfiles.rackspacecloud.com/02_24_10.xlsx
>
Is it possible to display xval in cells E38 to I38 and yval in cells E39 
to I39?  That is left to right rather top to bottom.

It makes it possible to copy/paste.

Cheers!
0
Saxman
2/26/2010 7:13:38 PM
On 25/02/2010 20:01, Ron Rosenfeld wrote:

> 0.8, 0.3, 1.7, 4.9, 6.7
>
> It returns the following array:
>
>   1.64		-2.04
>   0.372021505	 1.233855745
>   0.866271579	 1.176435294
> 19.43352601	 3
> 26.896		 4.152
>
> For some of what you've posted:
>
> Slope	1.64
> R2	0.87
> Se	1.18

The LINEST gives me the value me the value 1.64 in F1, but how does one 
get to display the array?
0
Saxman
2/26/2010 7:34:10 PM
On 26/02/2010 19:34, Saxman wrote:
> On 25/02/2010 20:01, Ron Rosenfeld wrote:
>
>> 0.8, 0.3, 1.7, 4.9, 6.7
>>
>> It returns the following array:
>>
>> 1.64 -2.04
>> 0.372021505 1.233855745
>> 0.866271579 1.176435294
>> 19.43352601 3
>> 26.896 4.152
>>
>> For some of what you've posted:
>>
>> Slope 1.64
>> R2 0.87
>> Se 1.18
>
> The LINEST gives me the value me the value 1.64 in F1, but how does one
> get to display the array?

Meant to say when values are entered in A1:E1
0
Saxman
2/26/2010 7:37:49 PM
On Fri, 26 Feb 2010 19:34:10 +0000, Saxman
<john.h.williamsREMOVETHIS@btinternet.com> wrote:

>On 25/02/2010 20:01, Ron Rosenfeld wrote:
>
>> 0.8, 0.3, 1.7, 4.9, 6.7
>>
>> It returns the following array:
>>
>>   1.64		-2.04
>>   0.372021505	 1.233855745
>>   0.866271579	 1.176435294
>> 19.43352601	 3
>> 26.896		 4.152
>>
>> For some of what you've posted:
>>
>> Slope	1.64
>> R2	0.87
>> Se	1.18
>
>The LINEST gives me the value me the value 1.64 in F1, but how does one 
>get to display the array?

You enter it as an array.

For example:

1.  Select the range B3:C7
2.	B3 should be the active cell
3.  In the formula bar, enter:
	=LINEST(A1:E1,,,1)
4.  To "confirm" the formula, hold down the Control and Shift keys while
hitting <Enter>.  Excel will then
	a.  Enter the formula in the entire selected range.
	b.  Surround the formula with braces {} showing that it is an **array**
entered formula.
	c.  Display the results in each cell:


	
--ron
0
Ron
2/26/2010 9:06:47 PM
On 26/02/2010 21:06, Ron Rosenfeld wrote:

> You enter it as an array.
>
> For example:
>
> 1.  Select the range B3:C7
> 2.	B3 should be the active cell
> 3.  In the formula bar, enter:
> 	=LINEST(A1:E1,,,1)
> 4.  To "confirm" the formula, hold down the Control and Shift keys while
> hitting<Enter>.  Excel will then
> 	a.  Enter the formula in the entire selected range.
> 	b.  Surround the formula with braces {} showing that it is an **array**
> entered formula.
> 	c.  Display the results in each cell:
>

Thanks.
Set up the array thanks.

All singing and dancing!
0
Saxman
2/26/2010 11:16:14 PM
On Fri, 26 Feb 2010 23:16:14 +0000, Saxman
<john.h.williamsREMOVETHIS@btinternet.com> wrote:

>Thanks.
>Set up the array thanks.
>
>All singing and dancing!

Glad to help.  Thanks for the feedback.
--ron
0
Ron
2/27/2010 12:47:54 AM
Reply:

Similar Artilces:

SMART Regression installation
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) So I am taking a statistics class. Our textbook comes with a disk and instructions to install SMARTReg, which provides additional regression features for Excel. (Yes, I do have the analysis tool-pak installed.) However, the installation is for windows (it is an .exe file). I went to the website listed in the ReadMe file: www.gierus.ca/alex/smartreg but it is definitely not related to this program. <br><br>Is there a Macintosh version of this add-on available that anyone knows about? <br><br>Thanks. <br><...

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&...

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 ...

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 ...

using linear / non linear graphs to extract results
Hi, A frequent function of my job is to analyse data on graphs/charts to extract data for engineering data , i.e power / temperature/flowrates etc etc Is the a microsoft based programe i.e. Excel/Access that is capable of understanding data on a scaled X-Y axis that if an X value was entered in a cell, the programme would extract a corresponding Y value from a line on a graph ? If anyone has any woring examples of such a thing, I'd appreciate a link Thx Hi, If there is a linear relationship between X and Y use the function FORECAST It a power series is needed use LINEST - see examp...

Linear Regression
I have five numbers in: A1,B1,C1,D1,E1 Is there a function that will give me the linear regression of those numbers that can be put in D1, with charting etc? TIA Linear regression works with ordered pairs. row 1: 1,2,3,4,5 row 2: 2,4,6,8,<formula> formula in E2 : =FORECAST(E1,A2:D2,A1:D1) will return 10 Any use? best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Saxman" <john.h.williamsREMOVETHIS@btinternet.com> wrote in message news:XpidnXpInZMDMx7WnZ2dnUVZ8m1i4p2d@bt.com... > I have five numbers in: &...

Linear Equations
I am having difficulty graphing a linear equation in two variables by using three ordered pair 1/2 y = 2x + My ordered pair x 0 1 y 2 6 10 see as follow Pairs x (0, 2 (1, 6 (2, 10 I am unable to graph this problem and need help!!! Just select the range containing the ordered pairs and Insert|Chart. Jerry Angela Mitchell wrote: > I am having difficulty graphing a linear equation in two variables by using three ordered pairs > > 1/2 y = 2x + 1 > > My ordered pairs > x 0 1 2 > y 2 6 10 see as follows > > Pairs: > x y > (0, 2) >...

linear increments
Hi, I am kind of new to excel and was wondering if excel is capable of doing linear increments. I want increments that can vary, meaning in one case i want to go from 0 to 10 in increments of 2 and in other cases i want to go from 0 to 10 in increments of 5. The number of cells change. Is it possible for excel to do this w/o having to specify the number of cells needed? -- NewGuy100 ------------------------------------------------------------------------ NewGuy100's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27935 View this thread: http://www.excelforum.com...

Excel 2007; graph the best fit line from a regression analysis?
Try using this white space to ask you question with a bit more detail best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "mattz" <mattz@discussions.microsoft.com> wrote in message news:574CCC66-72EA-44D0-8D36-2147B6C7C279@microsoft.com... > ...

Graph two linear inequality equations
So the question is..... Any help or guidnace or direction to a web page from the gurus would be appreciated for getting it into excel so I can have a pretty graph. I can solve this with a stuby pencil and paper. Easy enough to get the X and Y coordinates. How do you put it into excel to graph? I looked online and find examples, but the more I look the more confused I get. 400j + 250X <= 20,000 (j=0 and x= 80) (j=50 and x =0) straight line 40j + 30X <= 2,160 (j= 0 x = 72) J = 54 X = 0) straight line j >= 0 x >=0 (0,72) X220 = 15,840 (54,0) X320 = 17,280 An electronics ...

Linear Optimization
how can I optimize with integer variables? Assuming you are using Solver, add a constraint that specifies the variables to be 'Int' (w/o the quotes). -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <E214DF54-5098-41E1-9DD9-74DD53017D30@microsoft.com>, FaranakNegahdar@discussions.microsoft.com says... > how can I optimize with integer variables? > ...

forced linear regression
Hello, I have a dataset with a (straight) line fit to it (with regression value and everything). My question is how do I take line with a different slope and force fit it to my data and optimise this new fit (i.e. get a new, obviously smaller, regression value that's a maximum for the forced slope with my data)? It's sort of the opposite of forcing a line through zero, I think. Anyway, is it possible, and if so where do I go to find out how to do it? I think you have to do it the old fashioned way. Attached is a example. If you don't have 'Solver...' on y...

Linear Line
Firstly, apologies as this will appear twice. I sent it via Google and they said it'll be 3-8 hours before posted, now if I had 3-8 hours that'd be useful! My question: I have 8 sets of x,y coordinates, is there a function that can tell me if all points are linear (i.e. form a straight line). Thanks in advance Mike Do your own google searches with Ron's add in http://www.rondebruin.nl/ -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Mike" <mrskowse@hotmail.com> wrote in message news:vhvm1693cg7400@corp.supernews.com... > First...

linear interpolation
Hi, I have a set of data that has monthly (or sometimes biweekly) measurements. However, I need to interpolate the daily values using the actual consecutive data. However, I do not want to put a best-fit line or curve through the data for the whole year. I want the program to pick between two consecutive values, and depending on their relationship (linear increase or decrease), fill in the gaps between them and spit out the corresponding values. For example, I have the following set, Date Value Jan. 10 0 Jan. 15 5 what I need is this: Date Value Jan. 10 0 Ja...

Minitab feature identifying outliers for regression analysis?
Hi! I hoped to find an Minitab similar feature in Excel 2007. Minitab can during multiple linear regression tell you which datapoints are outliers. Is something similar in Excel in some way? Greetins Bjorn I recommend you Google with search term: excel regression outliers best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "B D Jensen" <bjorn.d.jensen@gmail.com> wrote in message news:958ccdb5-76e6-4dca-a590-c6b4e6bb936b@q39g2000hsf.googlegroups.com... > Hi! > I hoped to find an Minitab similar feature in Excel 2007...

X Axis Linear Representation Problem
I have a project involving a hiking trail where I am trying to capture altitude on Y axis versus mile marker location on X axis. I can enter the data in adjoining columns and use the chart wizard but each mile marker value gets positioned along the X axis in uniform increments rather than an actual linear scale representation. In other words, mile markers 3.3 and the next mile marker (3.8) are the same distance apart on the X axis in the graphic representation as is marker 3.8 and the next one (12.5). This distorts the graphic representation of the actual linear distance between the...

Erroneous Regression on Residuals
When I correct for the collinearity of regressors in a multivariate regression by replacing with residuals from another regression, regression in Excel is not working. One or more of the coefficients in the multivariate system (incorrectly) go to 0, with a p-value of 1. When I have tried on other machines, it runs fine. It seems like my stat pack is flawed. Has anyone else encountered this? Is there a way to debug? Scott - You have not provided much information. What versions of Excel run fine, and what versions don't? What "stat pack" are you using? Excel's Regress...

Linear Fitting
Hello I have a 512*256 data file. What I want to be able to do is create a new matrix by doing the following First, I want to calculate the linear fit equation for each row (the equation is different for each row). Then, I want to replace every value in that row with the value stored there minus the linear fit equation, where x is just the index number for the row. I want to be able to do this for all 512 rows, ultimately creating a whole new data set Let me just provide you with an example so it's clear what I am asking. So in my data file there are 256 numbers in the first row. T...

How do I perform linear regression?
How or where do I perform linear regression - in the chart or worksheet? Do you mean simply where do you perform it, or do you mean how to do it in general? "kunlez" wrote: > How or where do I perform linear regression - in the chart or worksheet? kunlez wrote: > How or where do I perform linear regression - in the chart or worksheet? ------------ Lots of possibilities, depending on what you want to get out of it. The simplest way is to just plot your data on an X,Y chart and then ask Excel to add the trend line to the data. Right click on the data line of the chart an...

Trendline
I am using Excel 2007. I added a linear trendline in an Excel Chart and noted down the slope of the linear equation. When I used the same table in Excel 2003, the slope was different. I checked this out a lot of time and figured it out that Excel 2007 is giving value one digit short. e.g. slope in Excel 2003 is 20533 but in Excel 2007 it is 2053 (missing last digit of 3). I hope your technical staff will figure this out. r_sohail@ppl.com.pk rasha_great@hotmail.com ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To...

Linear Line in Graph
I am trying to create a graph for Patients per day. I would like it to graph my current years patients per day, and then do a trend/linear line for prior years pt's per day. Is there anyway to accomplish this using Access 2007? Thanks in advance for your help. Yes. If you want a more complete response, we need to know: - Your table(s) structure with significant fields and data types - Explain the difference between your current and previous year's lines - Do you want a point for each date -- Duane Hookom Microsoft Access MVP "Sr Accountant" wro...

linear trendline
Hi Can someone please help me with the trendline function in the graph. Does the linear regression represent a regular/classical fit or a Deming approach? With the regular approach the lines 'drawn' from the individual data points to the regression line are parallel to the Y-axis, Deming lines at an angle defined by the ratio of the relative errors of the 2 methods. How can I calculate the Deming method within Excel? Thanks Jo ...

linear fit
How can I obtain the propagation error (of tha a and b value ) when I do a linear fit of xi, and yi with excel? Tell us what A and B mean to you. Have a look at LINEST in the Help feature - it calculates standard errors in slope and intercept best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Mauro" <Mauro@discussions.microsoft.com> wrote in message news:65D211B2-8456-46FB-98DD-5D09B55CAB89@microsoft.com... > How can I obtain the propagation error (of tha a and b value ) when I do a > linear fit of xi, and yi wi...

I suck at basic linear math
It's been too long ago since first grade. I wish to calculate the intersecting point of 2 lines: typedef struct { POINT from; POINT to; } LINE; BOOL GetIntersectingPoint(LINE line1, LINE line2, LPPOINT lpPoint) { // if intersect, set lpPoint, return TRUE, otherwise return FALSE } Can someone fill in the body of this function? Lisa >It's been too long ago since first grade. >I wish to calculate the intersecting point of 2 lines: >Can someone fill in the body of this function? Google Lisa! http://www.geog.ubc.ca/courses/klink/gis.notes/ncgia/u32.html Dave ...

In Regression Analysis, How do you get a Correlation Matrix to be.
How do you get the Correlation Matrix for your variables to appear on the output screen? tjp32 - > How do you get the Correlation Matrix for your variables to appear on the > output screen? < Tools | Data Analysis | Correlation - Mike www.mikemiddleton.com ...