Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINEST using only some of the values in an array | Excel Worksheet Functions | |||
LINEST using only some of the values in an array | Excel Worksheet Functions | |||
LINEST using only some of the values in an array | Excel Worksheet Functions | |||
LINEST using only some of the values in an array | Excel Worksheet Functions | |||
LINEST using only some of the values in an array | Excel Worksheet Functions |