Multiple OLS Regressions


New Member
Jan 20, 2004
Hi Forum,

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


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?

Thanks a lot in advance!


Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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

P. S. I'm leaving now, but if you have doubts on the implementation post again and I'll answer later.
Upvote 0
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!
Upvote 0
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

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
End Sub
Upvote 0
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!
Upvote 0
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!
Upvote 0
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
Upvote 0

Forum statistics

Latest member

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
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
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