# Multiple OLS Regressions

#### realtylerdurdon

##### New Member
Hi Forum,

I have a long list of variables that looks like this one:

Year;IndustryCode;Sales;EBIT;...
...
...
...

I would like to do simple OLS regressions with LINEST() based on the combination of Year and IndustryCode. For each combination (e.g. Year 1999 and IndustryCode 23) I need a separate regression (all in all over 300 regressions). The number of records for these "blocks" varies and I need a trick to tell the LINEST() to choose the right arrays.

Any ideas?

Michael

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Anyone? There must be an easy way to do this.

Thanks!
Michael

Hi Michael

Solution in 2 steps.

First: build a table with all the unique combinations of Year and IndustryCode.

Use text to columns with delimeter semicolon to split the strings
Use advanced filter with options Copy to another table and unique records.

Second: write a macro to calculate the values of linest.

In the macro:

Loop through the unique combinations

For each combination:
....Use autofilter on original table to filter only the relevant records
....Feed linest with the values
....Write in the worksheet the results for that combination

Hope this helps
PGC

P. S. I'm leaving now, but if you have doubts on the implementation post again and I'll answer later.

Thanks for your reply! I have no problems with the importing of the data, but I am not skilled at VBA. I think I could try to record the macro, but I would need help to create a loop.

I will post again when I have recorded it. Thanks!
Michael

Edit : replaced by a following post

Edit: Erased.

Hi Michael

Here is the code and the example with the data. As you can see, the confirmation values and the calculated values match.

Hope it helps
PGC

Code:
``````Option Explicit
Option Base 1

Sub LinestYrCd()
Dim rY As Range, rCY As Range, rFilt As Range, vLin

ActiveSheet.AutoFilterMode = False
Set rY = Range("G2", Range("G" & Rows.Count).End(xlUp))

For Each rCY In rY
Range("A:D").AutoFilter Field:=1, Criteria1:=rCY.Value
Range("A:D").AutoFilter Field:=2, Criteria1:=rCY.Offset(, 1).Value
Set rFilt = Range("C2:D" & Range("C" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)

vLin = WorksheetFunction.LinEst(rFilt.Columns(1), rFilt.Columns(2))
rCY.Offset(, 2) = vLin(1)
rCY.Offset(, 3) = vLin(2)
ActiveSheet.AutoFilterMode = False
Next
End Sub``````
Consolidate1.xls
ABCDEFGHIJKLMNOP
1YearCodeYXYearCodeSlopeInterceptConfirmation
21999251211999250.80812611.23702
31999251651999231.21827412.4467YearCodeSlopeIntercept
419992518102000230.7513.083331999250.80812611.23702
519992524151999231.21827412.4467
61999231412000230.7513.08333
7199923163
8199923196
9199923249
101999232712
11200023131
12200023185
132000232415
14
15
Sheet1

Thank you very much, unfortunately I am not able to test it right now, but I will do this on the weekend and come back to you. I will have to adjust the code for more independent / right hand side variables, but I hope I can do this by myself.

Thanks again!
Michael

Hi pgc01,

thanks again for your help. Your original code works very well. I tried to adjust it to my needs, i.e. implementing more right hand side variables. Sadly I failed...

Could you please help me and adjust your code so that I can use more explaining variables? I will have to estimate equations with 3-5 right hand side variables, perhaps you can indicate with a comment in your code, which parts I have to adjust?

One other thing: Is it possible to include all statistics in the regression output? Right now your code puts out slope and intercept, but if you use LINEST by hand you get many other statistics like R², F statistic and standard errors for the coefficients, etc.

That would be very helpful. Hope you can help me out.

Thanks in advance and best regards!
Michael

Hi Michael

I'll be glad to help but I can only do it Sunday night or Monday.

I need some input from you.

When you use more explaining variables can I consider that the columns are sequential, meaning the first column Y, and the next the Xs, or will you need some different layout?

Another layout problem, maybe more complex is the result layout. Now with more explaining variables and the stats how do you want to have the result? can you post an example?

If you want to try to get the stats, just add 2 parameters True to the linest and the variable Lin will be loaded with all the values in a 2-dimentional array(5,2).

Kind regards
PGC

Replies
1
Views
216
Replies
0
Views
240
Replies
0
Views
464
Replies
0
Views
409
Replies
6
Views
182

1,217,357
Messages
6,136,081
Members
449,990
Latest member
orthodmd

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back