Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Regression In Excel

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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

View Answers     

Similar Excel Tutorials

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
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
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
Make Text to Uppercase
- 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.

Similar Topics







Hi Guys,

I am doing some statistical work with Excel 2007 and have been using the data analysis add-in 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 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?


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,


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?


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:

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





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


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'm trying to run a regression analysis in excel, but it keeps giving me this message: "Regression - LINEST () function returns error. Please check input ranges again." Basically, I have a whole bunch of historical data I'm running the Regression on. With all the historical data there, the regression runs just fine, but when I deleted some of the old data, it gave me this error message. I looked online for some solutions, and saw that it gives this error when there are blank cells, but I checked over and over again in the input ranges and there are NO blank cells. All of them are either populated with a 1 or 0. I even copied only the data I needed to run the regression analysis on to another spreadsheet, but it still gave me the same error. PLEASE HELP!!!! This is driving me crazy.


Hi All,

I am trying to do multivariate polynomial regression in excel, trying to correlate data of the form y=f(x1,x2) with second order polynomials:
Y = c + a1*x1 + a2*x1^2 + a3^x1^3 + b1*x2 + b2*x2^2 + b3*x2^3

Using the following command, I have achieved this:
=LINEST(A2:A10,B2:B10^{1,2,3,0,0,0}*C2:C10^{0,0,0, 1,2,3},TRUE,TRUE)

Can anybody tell me the theory Excel uses for this kind of regression?

Thank you very much in advance!

athanasioskolios


Hi everyone,
I'm looking for a vba-based stepwise regression model that I can use and amend for regression analysis. Does anybody know a link where I can find a source code, or, alternatively, a model spreadsheet that conducts the steps manually. I'm sure it must be possible to write an appropriate code for this. Cheers


Hi - I'm struggling with the math\function used in linear regression forecasting in Excel.

I have 8 qtrs of revenue (8 columns). I want to use linear regression to forecast into the next 8 qtrs (ending with 16 columns total - 8 actual, 8 forecast). Is there a, excel formulas I can use.

For simplicity, let's assume the data is in row 4...the actuals are in b through I and I want to forecast into J through Q.

Thanks!
alyssa


I am trying to write a macro that will run a regression analysis for me using ranges that I define in cells A1 (for the Y variable) and B1 (for the X variables). For example, a sample size of 250 will produce the following cell contents: A1 contains "$B$13:$B$263" and B1 contains "$C$13:$I$263". Changing the sample size for my regression will change the contents of cells A1 & B1.

In the VBA code below, the Y & X ranges for my regression are fixed, but I would like them to be variable and take on whatever "value" is in cells A1 & B1. I have spent hours searching message boards and sought advice from co-workers, to no avail. Please HELP!

Application.Run "Regress",
ActiveSheet.Range("$B$13:$B$263"),
ActiveSheet.Range("$C$13:$I$263"),
False, False, , _"macro regr", False, False, False, False, , False


Is there a way when creating a regression line on a XY scatter graph to have the regression line ignore a couple of data points, but still have the points show on the chart?


Hi,

I am trying to create a Macros to run a series of regressions, but my VBA skills are not extensive. When I recorded the Macro for the first regression I got the below
Code:

Sub Macro2()
'
' Macro2 Macro
'

'
     Application.Run "ATPVBAEN.XLAM!Regress", , , False, False, , , False _
        , False, False, False, , False
    Range("M20").Select
End Sub


Can Anyone tell me how this breaks down and where I can enter the X and Y ranges for the regression. I need to be able to put a seperate script in the ranges that selects all filled cells in the column for the ranges? I also need to know where I can enter the output range.

Thank you so much for any help you can give me with this, I greatly appreciate it.

Kind regards,

Guy


Hi everyone... I'm running a simple linear regression in excel that needs to have an intercept of 0. Thus, I ran the regression through Tools / Data Analysis and checked the "Constant is Zero" box. However, I also ran the regression line by plotting the points in a scatterplot, adding a trendline, checking "Set intercept = 0" and displaying equation and R square....

The problem is that the equations (really just slopes) are the same but the R squares are completely different. Does anyone have a clue why this could be or which one would be correct? I have always expected them to match (I did the same example except did not set intercept equal to 0 and they were equal). I have checked my inputs several times, and everything appears correct....

Any Clues to this difference in R squares????? Thanks!

Edit: I have Excel 2003... I have read that there is a known glitch before 2003 but it should be correct in the 2003 version....


Hi, everyone, My english name is Jack, I am from Shanghai, China. I am an analyst. Now I meet a VBA problem. I searched online and got many methods but no one can solve the problem. MrExcel is famous and I hope I can get help here.When I use my code to do regression, it always shows "error 1004". (notes: I loaded VBA tool pak, and ticked "ATPVBAEN.XLA" in "reference" under "tools" in VBE)
My code is as follows:
///////starts
Dim i, j As Integer
Cells(2, 2).Select
i = Selection.End(xlDown).Row
j = Selection.End(xlToRight).Column
Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range(Cells(2, 2), Cells(i, 2)), ActiveSheet.Range(Cells(2, 3), Cells(i, j)), False, False, , ActiveSheet.Range("$H$26"), False, False, False, False, , False
//////ends

The 3 solutions I found by google are as follows:
1. If Application.Version 11 Then
Application.Run "ATPVBAEN.XLAM!Regress",(Omit...)
end if
but it doesn't work
2. Microsoft website gave a method: replace (Application.Run "ATPVBAEN.XLA!Regress") with (Application.Run "'Analysis Toolpak - VBA'!Regress) or (Regression), it can't help either; from http://support.microsoft.com/kb/192642

3. add code: Application.Workbooks.Open "C:\Program Files\Microsoft Office\OFFICE11\Library\Analysis\atpvbaen.xla" (it's already my laptop path), but can't work.

Hope you can help me! Thank you very much!


I want to call the Regression capability in Excel's Data Analysis Tool Pack
from a VBA macro. I have checked atpvbaen.xls in the Reference pulldown menu
item in the macro and Tool Pack VBA in Add-ins. The call syntax begins with
Application.Run "ATPVBEAN.XLA!Regress" , but how do I find what the
remaining parameters are to include in the call, including specifying the
input and output ranges?

Thanks for your help
Phil






Hi,
I am wanting to write a macro which uses the excel multiple regression function (a part of the data analysis add-in).

I tried recording a macro while I selected the regression function (Tools> Data Analysis... etc.) which produced the following:

Code:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 9/07/2009 by
'

'
     Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("B2:B86"), _
        ActiveSheet.Range("$C$1:$D$86"), False, True, , ActiveSheet.Range("$I$2") _
        , False, False, False, False, , False
End Sub



However when I try to use this macro again I get a message saying: Runtime error '1004': ATPVBAEN.XLA cannot be found.

Any suggestions


Using Chart, Add Trendline, then selecting Linear in Excel to chart a linear
regression line on a series creates a new line on my chart with either a
positively or negatively sloped regression line.

Is there any way, using this built-in Excel feature, to convert the result
into degrees so that I can then label the regression line in degrees, for
example, 45 degrees?

My thanks for any ideas. Brad





the vba function "ATPVBAEN.XLA" that performs the regression for excel has a 16 variable limitation. is there a way to increase this, or does anyone know the password for it so that i can edit it (possibly)
Cheers


Hey there,

I have two arrays of data on which I would like to be able to run a polynomial regression in VBA.

However, when write the following in VBA:

Code:

 
Dim poly_3 as Variant
Dim arr1 as Variant
Dim arr2 as Variant
 
poly_3 = WorksheetFunction.LinEst(arr1, my_arr2 ^ {1, 2, 3})


I get a compile error, as {} are not valid characters. I've tried substituting parenthesis and leaving the brackets out completely, but that does not work either.

Anyone know how to perform a polynomial regression in VBA?

Working with two ranges of cells in a worksheet, the Linest function works just fine. ie, =LINEST(H18:H34, G18:G34^{1,2,3}), so I don't know why it would not be the same in VBA.



Office 2007


Surprisingly, that is the actual output of the error message when I run my macro.
To repeat it, it says, "Regression - Having trouble offsetting input/output references"

The two button choices that appear on the bottom are "OK" or "Help." The OK button allows the macro to keep running (until it finds that it cannot find the regression output that was supposed to be created) while the Help button brings up a help window but obviously cannot find any way to be helpful.

The error takes place during the regression code:

Code:

If Application.Version <= 11 Then
        Application.Run "ATPVBAEN.XLA!Regress", Sheets("Transpose") _
        .Range("$A$" & counter3 + 1 & ":$A$" & regressionWindow), Sheets("Index") _
        .Range("$A$" & counter3 + 1 & ":$D$" & regressionWindow), _
        False, True, , "fund", False, False, False, False, , False


This ONLY takes place when this code is part of a nested loop. It works perfectly fine on its own. I know this is a very complicated and in-depth question but I would appreciate any help.


Greetings,

I am trying to figure out how to calculate p, q, and M using cumulative and noncumulative sales data. I don't think a regular regression would work because of the shape of the data (S curve)- linear regression would not be an accurate predictor. Has anyone ever done anything like this in Excel (or SPSS) and could offer some suggestions or a template?

Thanks so much!

-Meryl

Hi Everyone,

I am trying to use linear regression to fill in values (Excel 2003). If I have two arrays of values (X,Y) with known X values and only the first and last Y values, how can I get Excel to calculate and fill in the unknown Y values for each other X value? I have attached a small excel file to illustrate the problem.

I see this as a linear regression problem and can get my calculator to do it but of course would like to be able to generate unkown values for many X values so the calculator becomes laborious.

It seems like such as simple task but I simply cannot seem to figure it out. Appreciate any help anyone can throw my way.

Matty @ MHS


Hello folks,

I have a set of data (see attached excel sheet). I performed the generic multiple regression analysis to determine the x-coefficients. All of this is in the attachment.

My Problem:
As you may see i have a negative coefficient for one variable and a positive for another. I want to have a positive coefficient for both variables. How can i do that?

Thanks
AM