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
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