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: khandar@gmail.com
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 ,
michel

[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

"michelxld" wrote:

>
> 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 ,
> michel
>
>
> --
> michelxld
> ------------------------------------------------------------------------
> michelxld's Profile: http://www.excelforum.com/member.php...o&userid=17367
> View this thread: http://www.excelforum.com/showthread...hreadid=496012
>
>

[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

--
Regards,
Tom Ogilvy


"jobxyz" <jobxyz@discussions.microsoft.com> wrote in message
news:63C9F3C2-BD38-4C51-8897-D6A071A059A6@microsoft.com...
> 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
>
> "michelxld" wrote:
>
> >
> > 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 ,
> > michel
> >
> >
> > --
> > michelxld
> > ------------------------------------------------------------------------
> > michelxld's Profile:
http://www.excelforum.com/member.php...o&userid=17367
> > View this thread:
http://www.excelforum.com/showthread...hreadid=496012
> >
> >