I use the Excel LINEST function to create linear models to forecast values for certain things. During the past few days I've tinkered with the idea of making the process of creating the forecast equation faster.
Creating the forecast value is not an issue with a model that contains just a few variables however as the number of variables grow the creation of the forecast equation can be quite tedious and time consuming, not only because of the number but also in part because of the LINEST output displayed reverse of the order of your variables. I was thinking that since the constants and variables were all in a contiguous ranges that there should be a way to automate this formula with a function.
As an aside, I did search for a solution that someone smarter than myself may have created. I didn't find such a solution potentially because the usage of of LINEST is a bit out of the mainstream.
Enough chat ... here's the thought process and how I constructed the function:
First I needed to read the Excel ranges into an array so I could manipulate the numbers to calculate.
The function needs to capture the ranges rConstants and rVariables
function myFcast(constants as range, variables as range)
Next, I need to create the arrays and load the ranges. Since I'm not real familiar with the intricacies of arrays, I was able to load horizontal ranges into 'vertical' arrays using the TRANSPOSE worksheet function. I thought there would be a way to load into a 'horizontal' array but I couldn't figure it out.
Dim ArrV() As Variant ' dimension array for Variables
Dim ArrC() As Variant ' dimension array for Constants
ArrV = Application.WorksheetFunction.Transpose(rVariables)
ArrC = Application.WorksheetFunction.Transpose(rConstants)
Now all that's left to do is to use the array contents to calculate the result of the forecast.
Dim C As Long ' counter for the number of Constants (one more than the number of Constants)
Dim V As Long ' counter for the number of Variables
In general we assemble the calculation by getting Constants (C) in reverse order with the Variables (V)in regular order.
V = 1 'set the V (variables) counter to 1
lmfcast = ArrC(UBound(ArrC, 1), 1) ' get the Intercept (last value) from the rConstants
For C = UBound(ArrC, 1) - 1 To 1 Step -1 ' the constants need to be multiplied to the corresponding variable
lmfcast = lmfcast + ArrC(C, 1) * ArrV(V, 1) ' take the result and add the product from the next pair (constant & variable)
V = V + 1
Finally hand the result to be returned by the function.
MyFcast = lmfcast
In my worksheet the formula
=V4 + U4*B5 + T4*C5 + S4*D5 + R4*E5 + Q4*F5 + P4*G5 + O4*H5 + N4*I5 + M4*J5
is now replaced with
More variables (up to 64)? No sweat.
The worksheet with the function is available. I hope you find this helpful. Reach out if you find an issue with it or have questions.
reference: LINEST function