
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
Similar Excel Video Tutorials
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
Make Text to Uppercase
 This macro will change all text within the selected cells to uppercase. It works only on selected cells within Microsoft
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?
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 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
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?
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
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 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!
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!
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 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?
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
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:
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, , False
If 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:
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:
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!
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
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
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
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'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.
In Excel 2003 there used to be a way of getting the program to take 2 arrays and have excel return a regression which included intercepts, Tstats, standard deviations, etc. (i.e. standard format). You could access it through the "data" toolbar.
Anyways, I've recently switch to the 2010 version of excel, and while I'm sure it has something that returns a regression table in standard format i'm having a difficult time finding it. Does anyone know where I can find it?
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 vbabased 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

