First of all Hi to everyone!
Im trying to write a LinEst VBA function but have some difficulties because I dont really know how to handle that LinEst correctly.
I also dont find any detailed documentation about that function in the web. So hopefully anyone here can help me.
Im using Excel 2010.
My model is one of the following type:
X = B_0 + B_1*Y + B_2*(Z)
The dependent variable X, the datas used to estimate my B's, i would like to pass to the function as a Range.
Y and Z Im going to calculate in the function.
So, when I pass X as Range to the function, and X consists of one row and twelve columns, does "Excel consider" that as a two dimensional Array like X() as Double, ReDim X(1, 12)?
Im asking that because Y and Z probably should be the same type of array as X when used in LinEst.
And assuming X, Y, Z are (1,12) Arrays, can LinEst handle that type at all?
Doing the same using a spreadsheet is pretty easy and looks like that (to make clearer what Im actually trying to do):
RGP is just what LinEst is called in german.
So, in the spreadsheet i have to pass the dependent variables Y and Z as one Range. Those I would now like to compute in the VBA function. Does that mean that I have to add Y and Z as one single Array with two rows and twelve columns? Like Dim YandZ() as Double, ReDim YandZ(2, 12)?
And then pass that to LinEst?
And what exactly is the return value of LinEst. Well obviously Ill recieve three B's, but do I have to save them in a one-row-three-columns-Array as in the spreadsheet?
Like Dim Results() as Double, ReDim Results(1,3),
Results = WorksheetFunction.LinEst(X, YandZ, True, False) ??
I know that are a lot of questions, and some of those may seem a bit stupid, but i just get the error message #name? all the time and so I have no idea what exactly Im doing wrongly.
Id be really thankful if someone more LinEstexperienced could help me here.
Greetings.
Im trying to write a LinEst VBA function but have some difficulties because I dont really know how to handle that LinEst correctly.
I also dont find any detailed documentation about that function in the web. So hopefully anyone here can help me.
Im using Excel 2010.
My model is one of the following type:
X = B_0 + B_1*Y + B_2*(Z)
The dependent variable X, the datas used to estimate my B's, i would like to pass to the function as a Range.
Y and Z Im going to calculate in the function.
So, when I pass X as Range to the function, and X consists of one row and twelve columns, does "Excel consider" that as a two dimensional Array like X() as Double, ReDim X(1, 12)?
Im asking that because Y and Z probably should be the same type of array as X when used in LinEst.
And assuming X, Y, Z are (1,12) Arrays, can LinEst handle that type at all?
Doing the same using a spreadsheet is pretty easy and looks like that (to make clearer what Im actually trying to do):
RGP is just what LinEst is called in german.
So, in the spreadsheet i have to pass the dependent variables Y and Z as one Range. Those I would now like to compute in the VBA function. Does that mean that I have to add Y and Z as one single Array with two rows and twelve columns? Like Dim YandZ() as Double, ReDim YandZ(2, 12)?
And then pass that to LinEst?
And what exactly is the return value of LinEst. Well obviously Ill recieve three B's, but do I have to save them in a one-row-three-columns-Array as in the spreadsheet?
Like Dim Results() as Double, ReDim Results(1,3),
Results = WorksheetFunction.LinEst(X, YandZ, True, False) ??
I know that are a lot of questions, and some of those may seem a bit stupid, but i just get the error message #name? all the time and so I have no idea what exactly Im doing wrongly.
Id be really thankful if someone more LinEstexperienced could help me here.
Greetings.
