write values to WorksheetFunction.Linest

[David]

I'm writing variant arrays to Linest with the code below
I'm taking a convoluted route by converting the array elapsedTime()
to a Variant Array 'Xs' via a range 'DeltaT'
I would prefer to use elapsedTime() more directly in Linest?
Any advice will be appreciated

Dim DeltaT as Range, DateTime as Range
Dim elapsedTime(1 to 5), i as Integer
Dim Xs as variant, Ys as Variant, output as variant
Set DateTime = Range("myrng") ' 5 x 1 range
For i = 1 To 5
elapsedTime(i) = DateTime(i) - DateTime(1)
DeltaT(i) = elapsedTime(i) ' populate temporary range
Next i
Ys = Range("myData").value
Xs = DeltaT.value ' transfer temporary range values to variant array
output = WorksheetFunction.LinEst(Ys, Xs, , 1)

 

[Patrick Molloy]

I'm not sure why you're trying to do it this way. If you have two cells with
times in, then just subtract one from the other.repicate for a coloumn. All
you nned to do then is add the linest function.

 

[Dougaj4]

David

You can assign the values in "myrng" directly into a variant array,
then subtract the first value from each of the array values:


Sub LinEstSub()

Dim DeltaT As Variant, DateTime As Variant
Dim i As Long
Dim Ys As Variant, output As Variant

DateTime = Range("myrng").Value2 ' 5 x 1 range
DeltaT = DateTime
For i = 1 To 5

DeltaT(i, 1) = DateTime(i, 1) - DateTime(1, 1) ' populate
DeltaT
Next i
Ys = Range("myData").Value2
Range("output").Value = WorksheetFunction.LinEst(Ys, DeltaT, , 1)

End Sub

Doug jenkins
http://newtonexcelbach.wordpress.com/

 

[David]

Patrick,
Thanks for your response
I'm using 2 arrays to feed XY data to a chart
one of them is a dynamic array: elapsedTime()
no problem with the next 2 lines:
ActiveSheet.ChartObjects("SkipHeight3").Chart _
.SeriesCollection("Observed values").XValues = elapsedTime

I would like to do similar for the Linest function, ie:
output = WorksheetFunction.LinEst(Ys, elapsedTime, , 1)
where Ys is a variant array of data and elapsedTime is my dynamic array.
Sadly this does not work.
I'm currently creating a range and variant array for the Xs
(to replace elapsedTime()) in the Linest function
that I otherwise do not need!