write values to WorksheetFunction.Linest
 

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)

write values to WorksheetFunction.Linest
 

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.


"David" wrote:

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)

write values to WorksheetFunction.Linest
 

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/



On Oct 20, 7:44*pm, David wrote:
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)

write values to WorksheetFunction.Linest
 

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!



"Patrick Molloy" wrote:

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.


"David" wrote:

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)