PDA

View Full Version : Solved: Autoconstruct Forecast Formula from Linest function based on number of variables



Hamond
07-10-2009, 04:24 AM
Hi

I am using the linest array function to estimate regression coefficients.

However in each regression I run the number of X variables/inputs are different which mean I need to manually change the forecast equation to accommodate each time the number of input variables change.

I'm looking for a way to automate construction of the forecast equation based on the number of Y variables in the regression, preferably using VBA.

Currently I have the coefficients that I need to use in the forecast equation laid in row 6.

B6 always includes the intercept, and then the rest of the columns C6, D6, E6 etc are either empty or contain a coefficient depending on the number of variables. So if there are 2 X variables then C6, D6 and E6 will have coefficients. The forecast is in column K, the first observation is always in row 14.

The forecast equation is shown in column K.

To forecast the first observation for Y , the forecast equation is C6+ D6*D14+E7*E14. If the number of variables increases to 5, then there would be two more terms to the existing equation (F7*F14, G7*G14).

I'm looking for a way to autoselect the right number of parameters in the forecast equation in row K14.

I can use the follwing to work out the number of varaibles

As a starter, I've worked out a way to work out the number of variables that need to be included but not sure how to do the rest - Yvariables


columnnum = Range("B1").End(xlToRight).Column
Yvariables = columnnum - 2


Many Thanks,

Hamond

mdmackillop
07-10-2009, 08:57 AM
I'm not up on the maths, but try this

Option Explicit
Sub Params()
Dim Par As Long, Fmla As String, i As Long

Par = Application.CountA(Range(Cells(1, 3), Cells(1, 10)))
Fmla = "=$B$6"
For i = 2 To Par
Fmla = Fmla & "+" & Cells(6, i + 1).Address & "*" & Cells(14, i + 1).Address(0, 0)
Next
Cells(14, "P").Formula = Fmla
Range(Cells(14, 1), Cells(14, 1).End(xlDown)).Offset(, 15).FillDown

End Sub

Hamond
07-11-2009, 04:11 AM
Thanks mdmackillop for coming up with the code. Clearly I think you don't need to worry about the underlying maths, it looks like you know exactly what I'm trying to do in terms of the process!

Anyway, the code looks perfectly logical to me (the parts I understand) but I couldn't get it to work in terms of including all the terms.

For example when you run it based on the example I gave you, it exlcudeds the last parameter in column E6 even though there is both a title and data in that column. I made columns D & E completely blank, but when I ran the macro it only included B6 in the equation instead of B6 and C6. It looks like it always excludes the last column from the forecast equation, I'm sure it just needs a minor tweak somewhere but I couldn't work it out.

Would greatfull if you could have a look.

Thanks

Hamond

mdmackillop
07-11-2009, 04:25 AM
Option Explicit
Sub Params()
Dim Par As Long, Fmla As String, i As Long

Par = Application.CountA(Range(Cells(1, 3), Cells(1, 10)))
Fmla = "=$B$6"
For i = 1 To Par
Fmla = Fmla & "+" & Cells(6, i + 2).Address & "*" & Cells(14, i + 2).Address(0, 0)
Next
Cells(14, "P").Formula = Fmla
Range(Cells(14, 1), Cells(14, 1).End(xlDown)).Offset(, 15).FillDown

End Sub

Krishna Kumar
07-11-2009, 11:41 AM
Hi,

In P14 and copy down,

=SUMPRODUCT($C$6:INDEX($C$6:$L$6,0,MATCH("zzzzzz",$C$1:$L$1)),C14:INDEX($C14:$L14,0,MATCH("zzzzzz",$C$1:$L$1)))+$B$6

HTH

Paul_Hossler
07-12-2009, 07:42 AM
if all you want is the 'curve fit' values, you can use the TREND() worksheet function


=TREND(B14:B75,C14:E75)


You can still use LINEST() to get the coeffecients and r, etc., but next to your col P I just Array Entered (ctrl+shift+enter) the above formula

That way tou don't need to know the number of x's

If you want, that can be automated by VBA, or by dynamic worksheet ranges

Paul

Hamond
07-12-2009, 02:12 PM
Mdmackillop,

The updated code works fine now - thanks.

Also thanks to the others for your solutions. I didn't realise you could use the trend function for more than two variables! That's eally usefull.

Thanks,

Hamond