using linest excel function from msaccess with multidimensional ar

[jobxyz]

Hi

I am trying to call the excel function linest from VB from an MS-Access
application.

the call
Slope1 =
Excel.WorksheetFunction.Index(Excel.WorksheetFunction.LinEst(y, x),1)

works fine as long as x is a one dimensional array.

But I need to do regression analysis for a multidimensional array (e.g.
I have x1,x2,x3,...,xn independent variables in the regression.)

so when I define x as say
dim x (10,2) as double

I get an error messages
"unable to get the linest property of the worksheet function class"

anyone know how to use linest for a multidimensioanl array ?

Thanks

 

[rock]

Dear All,

I am working on MS Excel. I want to know the cells which contains any
drawing objects created in Excel. Please reply.


Shitalkumar Ramdas Khandar
MailId: (e-mail address removed)
India

 

[michelxld]

Hello

for the linest Function , you may try


Dim y_connus(), x_connus()
y_connus = Array(5, 2, 1)
x_connus = Array(6, 3, 4)
Range("A1") = WorksheetFunction.LinEst(y_connus, x_connus)


Regards ,
miche

 

[jobxyz]

Thanks Michel

Your solution works fine for a simple X array. The problem arrises when x is
a 2D array - which is what I need

 

[Tom Ogilvy]

Worked fine for me. the formula in B12 was

=C12*1+D12*2+E12*3+5
copied down to 18. Then in the immediate window:

v = Range("B12:B18")
v1 = Range("C12:E18")
v2 = Application.Linest(v,v1,True,false)
? v2(1)
3
? v2(2)
2
? v2(3)
0.999999999999999
? v2(4)
5.00000000000001