Multiple OLS Regressions

realtylerdurdon

New Member
Joined
Jan 20, 2004
Messages
44
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?

Thanks a lot in advance!

Michael
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
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.
 

realtylerdurdon

New Member
Joined
Jan 20, 2004
Messages
44
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
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871

ADVERTISEMENT

Edit : replaced by a following post
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871

ADVERTISEMENT

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
 

realtylerdurdon

New Member
Joined
Jan 20, 2004
Messages
44
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
 

realtylerdurdon

New Member
Joined
Jan 20, 2004
Messages
44
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
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,676
Messages
5,549,371
Members
410,911
Latest member
AniEx
Top