Regression In Excel 


Regression In Excel  Excel 
View Answers 
I have two columns of data that I am suppose to do a regression of, I have no idea how regression works in excel. I am suppose to regress one against the other. Can you guys help me get started?
Ryan
Ryan
Similar Excel Video Tutorials
Mixed Cost Accounting Linear Regression  Ron's Class
 See how to do Linear Regression for determining what part of a mixed cost is a Fixed Cost and what part is a Variable Cost. Also See how to create a S ...
Igor's Physics Class Linear Regression Tricks
 Linear Regression
XY Scatter Diagram Chart Graph
X Y Scatter diagram Chart Graph
Add Axis Labels to X Y Scatter diagram Chart Graph
XY Scatter Diagram Chart Graph
X Y Scatter diagram Chart Graph
Add Axis Labels to X Y Scatter diagram Chart Graph
LINEST function
 Do Multiple Regression and Linear Algebra with the Array LINEST function. Also see how to use the SLOPE, INTERCEPT and the FORECAST functions for line ...
Helpful Excel Macros
Delete Empty Columns
 This macro will delete columns which are completely empty. This means that if there is no data within the entire column
 This macro will delete columns which are completely empty. This means that if there is no data within the entire column
Sort Data With Headers in Descending Order in Excel
 This Excel macro sorts data that has headers in descending order. This means that data is sorted Z to A and 10 to 1  o
 This Excel macro sorts data that has headers in descending order. This means that data is sorted Z to A and 10 to 1  o
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel  AutoFilter
 This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
 This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Make Text to Uppercase
 This macro will change all text within the selected cells to uppercase. It works only on selected cells within Microsoft
 This macro will change all text within the selected cells to uppercase. It works only on selected cells within Microsoft
Reverse Row or Column Order in a Worksheet
 This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
 This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Similar Topics
Hi,
I'm interested in running more than one regression (note: NOT multiple regression analysis). For instance, I have 1,000 different SKUs that I want to regress against sales forecasts to find a regression equation for each SKU as well as keep some of the information that Excel provides (T stat, R2, etc.). Is there a way to do this in Excel?
I'm interested in running more than one regression (note: NOT multiple regression analysis). For instance, I have 1,000 different SKUs that I want to regress against sales forecasts to find a regression equation for each SKU as well as keep some of the information that Excel provides (T stat, R2, etc.). Is there a way to do this in Excel?
Hi Guys,
I am doing some statistical work with Excel 2007 and have been using the data analysis addin to do some regression. In terms of a standard linear regression, one can enter the =slope(known y's , known x's) formula to find the regression coefficient for x. My regression is slightly more complicated as it has multiple independent variables (i.e. the y variable is not just a function of x, but also of another variable, say z). I can perform a multiple regression easily but I would like to be able to store the coefficients of the multiple regression as I must eventually add the coefficient estimate for x and z together. The multiple regression output is in a standard format however as I have to repeat this process for 1500 firms (one regression for each) it would be much more convenient if excel just reported the two coefficients for each regression. That way I could copy a formula across all firms and not use the standard regression window 1500 times.
Is there anyway I can perform the multiple regression formulaically, outputting just the two coefficients I require from each regression?
I apologise for the long winded post. Any help is very much appreciated. Thanks in advance.
I am doing some statistical work with Excel 2007 and have been using the data analysis addin to do some regression. In terms of a standard linear regression, one can enter the =slope(known y's , known x's) formula to find the regression coefficient for x. My regression is slightly more complicated as it has multiple independent variables (i.e. the y variable is not just a function of x, but also of another variable, say z). I can perform a multiple regression easily but I would like to be able to store the coefficients of the multiple regression as I must eventually add the coefficient estimate for x and z together. The multiple regression output is in a standard format however as I have to repeat this process for 1500 firms (one regression for each) it would be much more convenient if excel just reported the two coefficients for each regression. That way I could copy a formula across all firms and not use the standard regression window 1500 times.
Is there anyway I can perform the multiple regression formulaically, outputting just the two coefficients I require from each regression?
I apologise for the long winded post. Any help is very much appreciated. Thanks in advance.
Hello.
Can anyone explain how Excel computes the coefficients step by step when carrying out the full regression procedure for several variances (multiple regression).
Please note I said 'multiple regression', that is including several variables in the regression model and not just 2 as in a conventional linear regression (in which case the coeffiecients could simply be calculated as the slopes with the =SlOPE function.
Regards,
Chris
I dont suppose anyone knows if it is possible to do binary logistic regression well as ordinal logistic regression in excel?
Any pointers would be appreciated.
Cheers
Jonathan
Any pointers would be appreciated.
Cheers
Jonathan
I am trying to do multiple regression in excel. However, the only regression formula I can find (using the analysis toolpack), will only allow single regression (I think). Anyone?
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 pvalue 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?
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 pvalue 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?
I am trying to produce a parabolic regression (quadratic spline) for the closing price a stock. Please see below for the equation and click on the link for an examples of how it would look. I have no idea how to apply it to an Excel spreadsheet so I can study it against the stock's closing price and need someone's help to get things started.
This script calculates a parabolic regression (Quadratic Spline) of the data series based on the following 2nd order polynomial function: y(x) = b0 + b1*x + b2*x*x where b0, b1 and b2 are the calculated regression coefficients.
Sample of what the end result should look like:
http://www.sranalyst.com/i/ParabolicReg.gif
For example I would want to see what a 34 period parabolic regression plotted on top of the closing price of a stock. I have also enclosed a sample of the stock's closing price for review.
Thanks, Steven
This script calculates a parabolic regression (Quadratic Spline) of the data series based on the following 2nd order polynomial function: y(x) = b0 + b1*x + b2*x*x where b0, b1 and b2 are the calculated regression coefficients.
Sample of what the end result should look like:
http://www.sranalyst.com/i/ParabolicReg.gif
For example I would want to see what a 34 period parabolic regression plotted on top of the closing price of a stock. I have also enclosed a sample of the stock's closing price for review.
Thanks, Steven
Good Afternoon,
I am a college student working on a project for a finance class and have started to become somewhat desperate because I cannot figure out a certain problem. I am sorry to be a bother, but I just would like some advice as to where to start.
I have a large amount of data on options prices and need to run a regression for each day in the past and then use the beta coefficients to create a column of data for each day.
I have attached a couple days of the data. Basically, I need to regress column M on columns NR for each date (the first one is 1/25/06 in the attached file) and then output the beta coefficients columns ZAD. All options for a given day should have the same beta coefficients. So, I need to be able to go through the data and run a regression for each day and then output the beta coefficients for that day in the appropriate columns. I have been using an addin OLS reg to do the regressions, and it works well, I just don't know how to make it dynamically chose the appropriate data fields to regress for each day and then put these coefficients into the right columns. Each day has a different number of options available and thus a different amount of data to regress so that complicates the matter.
If anyone could take a look, I would greatly appreciate it.
Thanks a lot,
Austin
I am a college student working on a project for a finance class and have started to become somewhat desperate because I cannot figure out a certain problem. I am sorry to be a bother, but I just would like some advice as to where to start.
I have a large amount of data on options prices and need to run a regression for each day in the past and then use the beta coefficients to create a column of data for each day.
I have attached a couple days of the data. Basically, I need to regress column M on columns NR for each date (the first one is 1/25/06 in the attached file) and then output the beta coefficients columns ZAD. All options for a given day should have the same beta coefficients. So, I need to be able to go through the data and run a regression for each day and then output the beta coefficients for that day in the appropriate columns. I have been using an addin OLS reg to do the regressions, and it works well, I just don't know how to make it dynamically chose the appropriate data fields to regress for each day and then put these coefficients into the right columns. Each day has a different number of options available and thus a different amount of data to regress so that complicates the matter.
If anyone could take a look, I would greatly appreciate it.
Thanks a lot,
Austin
Hi all,
My problem is that I have a number of countries for which I have a couple of observations (y,x) on which I want to make a 3th degree polynomium regression. I use the function LINEST and can doublecheck the results by plotting in the observations in a graph and ask for the buildin application to do the regression. I most cases my result is the same as that regression, but for some reason that I cannot figure out, one (Country7) gives me a completely different regression. Want I get using LINEST is obviously not in acordance with the observation. Please take a look at the attachment.
I would be very happy if someone could figure out, what is wrong.
Thanks in advance.
Timo
I have a set of data that makes a perfectly horizontal line. When I plot it, add the trendline and display R squared, I get 0. When I use the regression (under tools, data analysis), it gives an R2 of 1! I know the R2 should be zero, but why is the regression tool saying 1? I am using Excel 2003 and am not checking "constant equals zero". Thanks to anyone who can help!
Dear Richard
As discussed with you, I would like calculate a series of regression using the data analysis tool of the excel. But as you know if I want to run the regression using the data analysis tool, I have to manually enter the value of x AND Value of Y and then select the cell. As I need to run the regression for 950 regressions. I need a quick solution in this regards. I have organised the data which X variable and Y variable as in column wise. But I need a way so that I can get the output of 950 regressions and the two values retrieve from each regression output in an excel file.
I will attach the details but the file is really big.
hello
ive not done much with regression analysis in general, but to all the pros and gurus out there, question regarding regression analysis.
i have sales revenue for sales employees over the past year (each month).
is it worth doing some form of regression analysis or chart?...and if so, how do i go about doing it? thxs alot!
ive not done much with regression analysis in general, but to all the pros and gurus out there, question regarding regression analysis.
i have sales revenue for sales employees over the past year (each month).
is it worth doing some form of regression analysis or chart?...and if so, how do i go about doing it? thxs alot!
Hey.
I was wondering if you guys could help me with an excel problem? I really hope there are some excel fanatics in here...
I know how to obtain the values from the net and get the lines. What I don't know how to do is forecast 20 periods into the future using the regression equation. Could you guys instruct me on that?
1) This is the problem.
From yahoo, obtain a series of prices for an individual stock or for the Dow Jones Industrial or some other standard average.(at least 100 data points). Create an appropriate chart of the data. Fit three trend lines to the data, using technology. Include the equation and R2 for all models. Also, use the model that best fits the data to forecast 20 periods into the future, using technology and the regression equation. I am suppose to have a seperate chart for each regression model.
I was wondering if you guys could help me with an excel problem? I really hope there are some excel fanatics in here...
I know how to obtain the values from the net and get the lines. What I don't know how to do is forecast 20 periods into the future using the regression equation. Could you guys instruct me on that?
1) This is the problem.
From yahoo, obtain a series of prices for an individual stock or for the Dow Jones Industrial or some other standard average.(at least 100 data points). Create an appropriate chart of the data. Fit three trend lines to the data, using technology. Include the equation and R2 for all models. Also, use the model that best fits the data to forecast 20 periods into the future, using technology and the regression equation. I am suppose to have a seperate chart for each regression model.
I have used excel 07 to run a regression of 4 independent variables and a Y variable. I am looking to test for multicollinearity from the regression output given. Does anyone know if this is possible? What is the best way to go about doing this?
Thanks,
Thanks,
I want to make kind of a 3d regression and from a set of 3d points (XYZ  Coords) i want to calculate the interpolated plane,that fits best between the set of points.
So far I've only thought of making 3 linear regressions,one on each plane (XY,XZ,YZ) and combining them to create the plane.I don't know if there's an easier,more accurate and better way to do this.
Also what's the precision of the regression equation.it seems like it provides 4 decimals for the a coefficient and 2 for the b constant.Is that good,can i make it provide more accurate equation for the regression it draws?I mean does it use this exact equation or does it display an approximation of the regression it drew?
So far I've only thought of making 3 linear regressions,one on each plane (XY,XZ,YZ) and combining them to create the plane.I don't know if there's an easier,more accurate and better way to do this.
Also what's the precision of the regression equation.it seems like it provides 4 decimals for the a coefficient and 2 for the b constant.Is that good,can i make it provide more accurate equation for the regression it draws?I mean does it use this exact equation or does it display an approximation of the regression it drew?
Hi,
I would like to automate regression analysis, as done in XL 2007 .. Data/Data Analysis/Regression using vba. When I change dropdown lists, the results automatically appear in a predefined area of worksheet.
Your assistance would be appreciated.
Thanking you in advance.
Thank you
I would like to automate regression analysis, as done in XL 2007 .. Data/Data Analysis/Regression using vba. When I change dropdown lists, the results automatically appear in a predefined area of worksheet.
Your assistance would be appreciated.
Thanking you in advance.
Thank you
Hi, I am trying to create a macro that will run a regression over a specific amount of data. Each row in the sheet corresponds to a month. I want the regression to start out with a range over the last 18 months, in my case D54:D71, and increment by one everytime the regression in run. The way the sheet is set up I would need D53:D71 for 19 months, D52:D71 for 20 months and so on. I have already written the macro to work for 18 months (seen below) but if you have any ideas on how to change the range each time it would be helpful. Also the total macro has 9 other regressions on 9 different sheets so it would have to be able to change the range for all of them.
VB:
VB:
Application.Run "ATPVBAEN.XLAM!Regress", Worksheets("Home Equity").Range("$D$54:$D$71"), Worksheets("Home Equity").Range("$F$54:$K$71"), False, False, , Worksheets("Regression Data").Range("$A$29:$G$54"), False, False, False, False, , FalseIf you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
I borrowed the function from shg in this thread to test if a sheet exists but it is always returning false.
http://www.excelforum.com/excelprog...etexists.html
I am including the entire code. It does a regression analysis on data for a second degree polynomial. In doing so, it creates a worksheet called Regression. So I test first to see if this sheet already exists within the workbook and then ask the user to delete it if it does. I've stepped through the function and the function is always returning False whether Regression exists or not. Thanks in advance. Code:
http://www.excelforum.com/excelprog...etexists.html
I am including the entire code. It does a regression analysis on data for a second degree polynomial. In doing so, it creates a worksheet called Regression. So I test first to see if this sheet already exists within the workbook and then ask the user to delete it if it does. I've stepped through the function and the function is always returning False whether Regression exists or not. Thanks in advance. Code:
Sub Regression() ' ' Regression Macro ' Dim OrgSheet As String If SheetExists("Regression") Then MsgBox ("Regression Worksheet exists. Delete that worksheet before continuing") Else OrgSheet = ActiveSheet.Name Application.Run "ATPVBAEN.XLAM!Regress", Range("B11:B17"), Range("D11:E17"), False, False, , _ "Regression", False, False, False, False, , False 'requires VBA Analysis add in Sheets(OrgSheet).Select Range("L2").Select End If End Sub Function SheetExists(sWks As String, Optional wkb As Workbook) As Boolean On Error Resume Next SheetExists = IIf(wkb Is Nothing, ActiveWorkbook, wkb).Sheets(sWks) Is Nothing End Function
I have been reading around the forum trying to figure out the solution to this, but was unable to, so I thought I would join and post!
I am doing cash projections for work, and I use a regression to do them. I want to make this work via macro in order to avoid doing it every month. I would like to have my Macro use a Y and X range that automatically goes to the bottom of the data (i.e. Month 1 to row 54, month 2 to row 55, etc. or wherever the end of the data happens to be)
The regression output needs to have "labels" checked.
My X and Y values come from the worksheet called "Regression Data"
The Y data is currently C1:C54
The X data is currently D1:S54
(Rows will be added to these columns each month)
The output is on the worksheet "Regression Output"
The Output range is just this entire worksheet. (1:1048576)
I attempted to simply record the macro, but kept getting errors about having no "Y" data, and when I looked at the code, I was unable to fix this and it did not appear that the macro was recording my input of the data range.
I am not sure if this will happen automatically, but the new regression (ran via macro) needs to simply replace the old macro (i.e. go right on top of it, with the same data in the same cells)
I will post my code below, if anyone is able to help me out with this. I would greatly appreciate it! Thank you!
Code:
I am doing cash projections for work, and I use a regression to do them. I want to make this work via macro in order to avoid doing it every month. I would like to have my Macro use a Y and X range that automatically goes to the bottom of the data (i.e. Month 1 to row 54, month 2 to row 55, etc. or wherever the end of the data happens to be)
The regression output needs to have "labels" checked.
My X and Y values come from the worksheet called "Regression Data"
The Y data is currently C1:C54
The X data is currently D1:S54
(Rows will be added to these columns each month)
The output is on the worksheet "Regression Output"
The Output range is just this entire worksheet. (1:1048576)
I attempted to simply record the macro, but kept getting errors about having no "Y" data, and when I looked at the code, I was unable to fix this and it did not appear that the macro was recording my input of the data range.
I am not sure if this will happen automatically, but the new regression (ran via macro) needs to simply replace the old macro (i.e. go right on top of it, with the same data in the same cells)
I will post my code below, if anyone is able to help me out with this. I would greatly appreciate it! Thank you!
Code:
Sub Regression() ' ' Regression Macro ' ' Application.Run "ATPVBAEN.XLAM!Regress", , , False, True, 95, , False _ , False, False, False, , False Range("H13").Select ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Cells.Select Cells.EntireColumn.AutoFit End Sub
(Running XP/2007, very new to formulas):
I have a cell (I12) that performs a calculation: =IF(E9=0,0,(E11E13)/((E90.25*E9)*0.00018939)).
I have a hyperlink in another cell that goes to another sheet in the same workbook:'DRegion 3 Delta'.
On DDelta page, B2 has a calculation: =171*('Rural Regression Calc'!J7^0.87)*('Rural Regression Calc'! I12 ^0.25)*('Rural Regression Calc'!J9^0.52).
This is fine except when cell I12 =0. The error is: #DIV/0!. I corrected this with: =IF( I12 =0,0,(171*('Rural Regression Calc'!J7^0.87)*('Rural Regression Calc'! I12 ^0.25)*('Rural Regression Calc'!J9^0.52))), it works.
My Problem: when I12 does not=0 , the hyperlink to the DDelta sheet /B2 cell isn't updated to reflect the computation change. It remains 0. My goal is to have B2 dynamically update/display correct information as I12 changes.
Can someone please help me? I don't know VB. I appreciate it very much!
I have a cell (I12) that performs a calculation: =IF(E9=0,0,(E11E13)/((E90.25*E9)*0.00018939)).
I have a hyperlink in another cell that goes to another sheet in the same workbook:'DRegion 3 Delta'.
On DDelta page, B2 has a calculation: =171*('Rural Regression Calc'!J7^0.87)*('Rural Regression Calc'! I12 ^0.25)*('Rural Regression Calc'!J9^0.52).
This is fine except when cell I12 =0. The error is: #DIV/0!. I corrected this with: =IF( I12 =0,0,(171*('Rural Regression Calc'!J7^0.87)*('Rural Regression Calc'! I12 ^0.25)*('Rural Regression Calc'!J9^0.52))), it works.
My Problem: when I12 does not=0 , the hyperlink to the DDelta sheet /B2 cell isn't updated to reflect the computation change. It remains 0. My goal is to have B2 dynamically update/display correct information as I12 changes.
Can someone please help me? I don't know VB. I appreciate it very much!
Hi everyone,
I would like to run a multiple linear regression in vba. I have one dependent and three explanatory variables. I will have to use a macro of some kind, since I need to run too many regressions to do it manually. To simplify things a little bit:
 There will always be exactly three independent variables
 There are no missing values
 The data is allways numerical
I've already got four ranges defined: Yrange, X1range, X2range, X3range. I would like to take these ranges as input parameters for the regression model. The only two parameters I need are Sum Square for Regression (SSR) and the degrees of freedom. I understand that you can use excel's matrix formulas to calculate some of the input parameters, but one doesn't really get around vba. Does anybody know where I can find a (simple) source code allowing me to conduct a regression with three input parameters? Many thanks in advance
I would like to run a multiple linear regression in vba. I have one dependent and three explanatory variables. I will have to use a macro of some kind, since I need to run too many regressions to do it manually. To simplify things a little bit:
 There will always be exactly three independent variables
 There are no missing values
 The data is allways numerical
I've already got four ranges defined: Yrange, X1range, X2range, X3range. I would like to take these ranges as input parameters for the regression model. The only two parameters I need are Sum Square for Regression (SSR) and the degrees of freedom. I understand that you can use excel's matrix formulas to calculate some of the input parameters, but one doesn't really get around vba. Does anybody know where I can find a (simple) source code allowing me to conduct a regression with three input parameters? Many thanks in advance
Who can help me with three questions on analyseit toolpack when performing passing and bablok orthogonal regression.
1. There are two menus by which to start with passing and bablok A. via agreement and B. via regression. They give similar but not identical results. Which one should i use
2. How can I get a correlation coefficient using passing bablok regression
3. How can I calculate the error in slope and iontercept using passing bablok regression?
Thanx for your help.
Helen
1. There are two menus by which to start with passing and bablok A. via agreement and B. via regression. They give similar but not identical results. Which one should i use
2. How can I get a correlation coefficient using passing bablok regression
3. How can I calculate the error in slope and iontercept using passing bablok regression?
Thanx for your help.
Helen
Hello,
i have a problem with a regression.
I have a standard regression with about 10 years of data.
ex:
r = a + Beta*X + e
and i wish to calculate the beta, this is pretty simple, but what i want to do is at any given periode, only calculate the Beta based on the previous 100 days. Is this possible to do in excel? And is it possible to get the results graphically?
If not in excel, is it easily enough to do in another program?
thanks for your help
i have a problem with a regression.
I have a standard regression with about 10 years of data.
ex:
r = a + Beta*X + e
and i wish to calculate the beta, this is pretty simple, but what i want to do is at any given periode, only calculate the Beta based on the previous 100 days. Is this possible to do in excel? And is it possible to get the results graphically?
If not in excel, is it easily enough to do in another program?
thanks for your help
Hi All,
I am trying to approximate a surface with the following equation:
ax^3 + by^3 +cx^2y^2 + dx^2y + exy^2 + fx^2 + gy^2 + hxy + ix + jy + k.
As you have here 2 independent variables, does anybody of you, know how you can solve for the coefficients with a multidimensional regression in excel. I know how the regression tool works more or less and where to find it, but I don't know how I can apply it to a multidimensional equation??
A step by step example would be wonderful! You can find the raw data in the attachement.
Many thanks in advance and cheers,
Martin
P.s. I use Exel 2003 and 2007.
I am trying to approximate a surface with the following equation:
ax^3 + by^3 +cx^2y^2 + dx^2y + exy^2 + fx^2 + gy^2 + hxy + ix + jy + k.
As you have here 2 independent variables, does anybody of you, know how you can solve for the coefficients with a multidimensional regression in excel. I know how the regression tool works more or less and where to find it, but I don't know how I can apply it to a multidimensional equation??
A step by step example would be wonderful! You can find the raw data in the attachement.
Many thanks in advance and cheers,
Martin
P.s. I use Exel 2003 and 2007.
Greetings from not so sunny FL (at least today ). I'm a StateCertified Residential Real Estate Appraiser attempting to learn Excel spreadsheets in regard to regression analysis. I recently purchased an awesome regression analysis tool but.....find myself in need of Excel training first. Any direction would be greatly appreciated.
Happy to have found this forum