Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of array formula
I would like to use the worksheetfunction.linEst function in excel VBA, but
need help on how to identify that the function returns an array rather than a single value. Thank you. -- Ho-Shu |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of array formula
By selecting several cells first, like 5 rows x 2 columns, for example.
Enter the LINEST function, press ctrl/shift/enter, see different results in the cells. There's not a way to inherently know if a function should be entered as an array formula without using help (excel's or other folks' or newsgroups, or trial & error --) HTH Bob Umlas Excel MVP "hsPipe" wrote in message ... I would like to use the worksheetfunction.linEst function in excel VBA, but need help on how to identify that the function returns an array rather than a single value. Thank you. -- Ho-Shu |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of array formula
Thanks, bob. However, my problem is I am trying to use the Linest function
in Excel VBA, and how to identify in the VBA code that the call to Linest is an array formula. -- Ho-Shu "Bob Umlas" wrote: By selecting several cells first, like 5 rows x 2 columns, for example. Enter the LINEST function, press ctrl/shift/enter, see different results in the cells. There's not a way to inherently know if a function should be entered as an array formula without using help (excel's or other folks' or newsgroups, or trial & error --) HTH Bob Umlas Excel MVP "hsPipe" wrote in message ... I would like to use the worksheetfunction.linEst function in excel VBA, but need help on how to identify that the function returns an array rather than a single value. Thank you. -- Ho-Shu . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of array formula
You can store the array result of LINEST in a Variant (the single
Variant will contain an array of Doubles). You can put that array to a range of cells or directly access the elements of the array: Dim Res As Variant Dim Dest As Range Set Dest = Range("L1:M5") Res = Application.WorksheetFunction.LinEst( _ Range("A2:A10"), Range("B2:B10"), True, True) ' put to worksheet Dest = Res ' access array directly Dim R As Long Dim C As Long For R = 1 To 5 For C = 1 To 2 Debug.Print Res(R, C) Next C Next R Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Mon, 8 Feb 2010 10:51:01 -0800, hsPipe wrote: I would like to use the worksheetfunction.linEst function in excel VBA, but need help on how to identify that the function returns an array rather than a single value. Thank you. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of array formula
Thank you. That is what I am looking for.
One additional basic VBA question. I have stored the x and y data from the spread sheet in variable arrays sXValue() and sYValue(), and I want to do "Least Square Fitting" calculations using the LinEst function for different ranges of x-y points within the sXValue and sYValue arrays. How do I achieve that in Excel VBA? -- Ho-Shu "Chip Pearson" wrote: You can store the array result of LINEST in a Variant (the single Variant will contain an array of Doubles). You can put that array to a range of cells or directly access the elements of the array: Dim Res As Variant Dim Dest As Range Set Dest = Range("L1:M5") Res = Application.WorksheetFunction.LinEst( _ Range("A2:A10"), Range("B2:B10"), True, True) ' put to worksheet Dest = Res ' access array directly Dim R As Long Dim C As Long For R = 1 To 5 For C = 1 To 2 Debug.Print Res(R, C) Next C Next R Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Mon, 8 Feb 2010 10:51:01 -0800, hsPipe wrote: I would like to use the worksheetfunction.linEst function in excel VBA, but need help on how to identify that the function returns an array rather than a single value. Thank you. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of array formula
Ho-Shu or hsPipe -
One way is to DIM two new arrays, write a loop to examine each point, depending on your criteria add the old point to the new arrays, and learn about REDIM. - Mike http://www.MikeMiddleton.com "hsPipe" wrote in message ... Thank you. That is what I am looking for. One additional basic VBA question. I have stored the x and y data from the spread sheet in variable arrays sXValue() and sYValue(), and I want to do "Least Square Fitting" calculations using the LinEst function for different ranges of x-y points within the sXValue and sYValue arrays. How do I achieve that in Excel VBA? -- Ho-Shu "Chip Pearson" wrote: You can store the array result of LINEST in a Variant (the single Variant will contain an array of Doubles). You can put that array to a range of cells or directly access the elements of the array: Dim Res As Variant Dim Dest As Range Set Dest = Range("L1:M5") Res = Application.WorksheetFunction.LinEst( _ Range("A2:A10"), Range("B2:B10"), True, True) ' put to worksheet Dest = Res ' access array directly Dim R As Long Dim C As Long For R = 1 To 5 For C = 1 To 2 Debug.Print Res(R, C) Next C Next R Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Mon, 8 Feb 2010 10:51:01 -0800, hsPipe wrote: I would like to use the worksheetfunction.linEst function in excel VBA, but need help on how to identify that the function returns an array rather than a single value. Thank you. . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of array formula
Thank you. I was hoping there is a more elegant solutio, such as the use of
range for variable arrays? -- Ho-Shu "Mike Middleton" wrote: Ho-Shu or hsPipe - One way is to DIM two new arrays, write a loop to examine each point, depending on your criteria add the old point to the new arrays, and learn about REDIM. - Mike http://www.MikeMiddleton.com "hsPipe" wrote in message ... Thank you. That is what I am looking for. One additional basic VBA question. I have stored the x and y data from the spread sheet in variable arrays sXValue() and sYValue(), and I want to do "Least Square Fitting" calculations using the LinEst function for different ranges of x-y points within the sXValue and sYValue arrays. How do I achieve that in Excel VBA? -- Ho-Shu "Chip Pearson" wrote: You can store the array result of LINEST in a Variant (the single Variant will contain an array of Doubles). You can put that array to a range of cells or directly access the elements of the array: Dim Res As Variant Dim Dest As Range Set Dest = Range("L1:M5") Res = Application.WorksheetFunction.LinEst( _ Range("A2:A10"), Range("B2:B10"), True, True) ' put to worksheet Dest = Res ' access array directly Dim R As Long Dim C As Long For R = 1 To 5 For C = 1 To 2 Debug.Print Res(R, C) Next C Next R Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Mon, 8 Feb 2010 10:51:01 -0800, hsPipe wrote: I would like to use the worksheetfunction.linEst function in excel VBA, but need help on how to identify that the function returns an array rather than a single value. Thank you. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
copy one array formula to an array range | Excel Programming | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming |