Application.WorksheetFunction.Linest - not working as expected.

[TomCon]

Any thoughts on this, how to make it work as a UDF using Application.WorksheetFunction.LinEst?

On the worksheet this formula functions correctly, returns a value.

=INDEX(LINEST((K2:K11*L2:L11),(J2:J11*L2:L11),FALSE),1,1)

Trying to wrap it in a VBA UDF, the VBA function is:

Function tst_linest(y As Range, x As Range, w As Range)
tst_linest = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst((y * w), (x * w), False), 1, 1)
End Function

But, when i call this function on the worksheet with this formula, it returns #VALUE

=tst_linest(K2:K11,J2:J11,L2:L11,FALSE)

 

[TomCon]

Any thoughts on this, how to make it work as a UDF using Application.WorksheetFunction.LinEst?

On the worksheet this formula functions correctly, returns a value.

=INDEX(LINEST((K2:K11*L2:L11),(J2:J11*L2:L11),FALSE),1,1)

Trying to wrap it in a VBA UDF, the VBA function is:

Function tst_linest(y As Range, x As Range, w As Range)
tst_linest = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst((y * w), (x * w), False), 1, 1)
End Function

But, when i call this function on the worksheet with this formula, it returns #VALUE

=tst_linest(K2:K11,J2:J11,L2:L11,FALSE)

SORRY THIS IS RETRACTED, I CAN SEE AN ERROR IN MY OWN STUFF.
I apologize.

PS - Is there a way to delete a thread you post? I can't find it.

 

[RoryA]

You have to use the report post button and then, depending on whether there are any responses or not, the moderators may delete it for you.