Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LINEST multiple known_x's separated arrays
Hello friends,
I'm trying to make a formula with LINEST, that use in the known_x's parameter, two separated arrays, my arrays are made with TRANSPOSE, and I want to join them together to create the table that needs the parameter. The arrays has the same amount of rows and one column. I need a user defined function to combine the arrays. =LINEST(TRANSPOSE(Sheet1!AH17:AR17),CombineArray(T RANSPOSE(Sheet1! DK17:DU17),TRANSPOSE(Sheet1!DJ17:DT17)),1,1) Please excuse my English. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LINEST multiple known_x's separated arrays
"Rickemil" wrote:
I need a user defined function to combine the arrays. =LINEST(TRANSPOSE(Sheet1!AH17:AR17), CombineArray(TRANSPOSE(Sheet1!DK17:DU17), TRANSPOSE(Sheet1!DJ17:DT17)),1,1) First, it is not necessary to change the orientation of the ranges. If your y-data are in the 1-by-11 row AH17:AR17 and your x-data were in the 2-by-11 rows DK17:DU18, you could simply write the following array-entered format (selecting the appropriate number of cells and pressing ctrl+shift+Enter instead of just Enter): =LINEST(AH17:AR17,DK17:DU18,TRUE,TRUE) (Note: I am not typing the "Sheet1!" prefix for brevity. You would write Sheet1!AH17:AR17 etc.) Second, by "combine the array", I assume you mean: create one 2-by-11 array of two 1-by-11 arrays for the purpose of __multiple__ linear regression, not concatenate two 11-element arrays into one 22-element array for the purpose of __simple__ linear regression. As you might know, the x-data must have the same number of elements as the y-data in the "principle direction" (row or column) as the y-data. Finally, your two ranges, DK17:DU17 and DJ17:DT17, are unusual insofar as they overlap. If that makes sense for your multiple linear regression model, fine. Otherwise, you might need to revisit what you are trying to do exactly. Be that as it may, instead of using a UDF, I would be inclined to use "helper cells". For example: IL1: =DK17 Copy IL1 across through IV1 IL2: =DJ17 Copy IL2 across through IV2 Array-enter: =LINEST(AH17:AR17,IL1:IV2,TRUE,TRUE) But if you really need a UDF (for example, because you are replicating the LINEST formula for several sets of y and x data), the following returns a 2-by-n array when passed two 1-by-n ranges (rows). Array-enter: =LINEST(AH17:AR17,combineRows(DK17:DU17,DJ17:DT17) The UDF: Function combineRows(r1 As Range, r2 As Range) As Variant Dim v1 As Variant, v2 As Variant Dim n As Long, i As Long v1 = r1 v2 = r2 n = UBound(v1, 2) If UBound(v1, 1) < 1 Or UBound(v1, 1) < 1 Or n < UBound(v2, 2) Then combineRows = CVErr(xlErrValue) Else ReDim v(1 To 2, 1 To n) For i = 1 To n v(1, i) = v1(1, i) v(2, i) = v2(1, i) Next combineRows = v End If End Function |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LINEST multiple known_x's separated arrays
El domingo, 17 de febrero de 2013 19:50:15 UTC-2, joeu2004 escribió:
"Rickemil" wrote: I need a user defined function to combine the arrays. =LINEST(TRANSPOSE(Sheet1!AH17:AR17), CombineArray(TRANSPOSE(Sheet1!DK17:DU17), TRANSPOSE(Sheet1!DJ17:DT17)),1,1) First, it is not necessary to change the orientation of the ranges. If your y-data are in the 1-by-11 row AH17:AR17 and your x-data were in the 2-by-11 rows DK17:DU18, you could simply write the following array-entered format (selecting the appropriate number of cells and pressing ctrl+shift+Enter instead of just Enter): =LINEST(AH17:AR17,DK17:DU18,TRUE,TRUE) (Note: I am not typing the "Sheet1!" prefix for brevity. You would write Sheet1!AH17:AR17 etc.) Second, by "combine the array", I assume you mean: create one 2-by-11 array of two 1-by-11 arrays for the purpose of __multiple__ linear regression, not concatenate two 11-element arrays into one 22-element array for the purpose of __simple__ linear regression. As you might know, the x-data must have the same number of elements as the y-data in the "principle direction" (row or column) as the y-data. Finally, your two ranges, DK17:DU17 and DJ17:DT17, are unusual insofar as they overlap. If that makes sense for your multiple linear regression model, fine. Otherwise, you might need to revisit what you are trying to do exactly. Be that as it may, instead of using a UDF, I would be inclined to use "helper cells". For example: IL1: =DK17 Copy IL1 across through IV1 IL2: =DJ17 Copy IL2 across through IV2 Array-enter: =LINEST(AH17:AR17,IL1:IV2,TRUE,TRUE) But if you really need a UDF (for example, because you are replicating the LINEST formula for several sets of y and x data), the following returns a 2-by-n array when passed two 1-by-n ranges (rows). Array-enter: =LINEST(AH17:AR17,combineRows(DK17:DU17,DJ17:DT17) The UDF: Function combineRows(r1 As Range, r2 As Range) As Variant Dim v1 As Variant, v2 As Variant Dim n As Long, i As Long v1 = r1 v2 = r2 n = UBound(v1, 2) If UBound(v1, 1) < 1 Or UBound(v1, 1) < 1 Or n < UBound(v2, 2) Then combineRows = CVErr(xlErrValue) Else ReDim v(1 To 2, 1 To n) For i = 1 To n v(1, i) = v1(1, i) v(2, i) = v2(1, i) Next combineRows = v End If End Function Thanks Joeu2004, your response help a lot. I did a variation of your UDF to add one more row. This is my approach, and work: Function combineRows2(r1 As Range, r2 As Range, r3 As Range) As Variant Dim v1 As Variant, v2 As Variant, v3 As Variant Dim n As Long, i As Long v1 = r1 v2 = r2 v3 = r3 n = UBound(v1, 2) If UBound(v1, 1) < 1 Or UBound(v1, 1) < 1 Or n < UBound(v2, 2) Then combineRows2 = CVErr(xlErrValue) Else ReDim v(1 To 3, 1 To n) For i = 1 To n v(1, i) = v1(1, i) v(2, i) = v2(1, i) v(3, i) = v3(1, i) Next combineRows2 = v End If End Function But, I dont understand why this part of the function repeat twice: UBound(v1, 1) < 1 Or UBound(v1, 1) < 1 Well, like I say you help me a lot, thanks for all. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LINEST multiple known_x's separated arrays
"Rickemil" wrote:
joeu2004 escribió: in message ... El domingo, 17 de febrero de 2013 19:50:15 UTC-2, joeu2004 escribió: "Rickemil" wrote: I need a user defined function to combine the arrays. =LINEST(TRANSPOSE(Sheet1!AH17:AR17), CombineArray(TRANSPOSE(Sheet1!DK17:DU17), TRANSPOSE(Sheet1!DJ17:DT17)),1,1) First, it is not necessary to change the orientation of the ranges. If your y-data are in the 1-by-11 row AH17:AR17 and your x-data were in the 2-by-11 rows DK17:DU18, you could simply write the following array-entered format (selecting the appropriate number of cells and pressing ctrl+shift+Enter instead of just Enter): =LINEST(AH17:AR17,DK17:DU18,TRUE,TRUE) (Note: I am not typing the "Sheet1!" prefix for brevity. You would write Sheet1!AH17:AR17 etc.) Second, by "combine the array", I assume you mean: create one 2-by-11 array of two 1-by-11 arrays for the purpose of __multiple__ linear regression, not concatenate two 11-element arrays into one 22-element array for the purpose of __simple__ linear regression. As you might know, the x-data must have the same number of elements as the y-data in the "principle direction" (row or column) as the y-data. Finally, your two ranges, DK17:DU17 and DJ17:DT17, are unusual insofar as they overlap. If that makes sense for your multiple linear regression model, fine. Otherwise, you might need to revisit what you are trying to do exactly. Be that as it may, instead of using a UDF, I would be inclined to use "helper cells". For example: IL1: =DK17 Copy IL1 across through IV1 IL2: =DJ17 Copy IL2 across through IV2 Array-enter: =LINEST(AH17:AR17,IL1:IV2,TRUE,TRUE) But if you really need a UDF (for example, because you are replicating the LINEST formula for several sets of y and x data), the following returns a 2-by-n array when passed two 1-by-n ranges (rows). Array-enter: =LINEST(AH17:AR17,combineRows(DK17:DU17,DJ17:DT17) The UDF: Function combineRows(r1 As Range, r2 As Range) As Variant Dim v1 As Variant, v2 As Variant Dim n As Long, i As Long v1 = r1 v2 = r2 n = UBound(v1, 2) If UBound(v1, 1) < 1 Or UBound(v1, 1) < 1 Or n < UBound(v2, 2) Then combineRows = CVErr(xlErrValue) Else ReDim v(1 To 2, 1 To n) For i = 1 To n v(1, i) = v1(1, i) v(2, i) = v2(1, i) Next combineRows = v End If End Function Thanks Joeu2004, your response help a lot. I did a variation of your UDF to add one more row. This is my approach, and work: Function combineRows2(r1 As Range, r2 As Range, r3 As Range) As Variant Dim v1 As Variant, v2 As Variant, v3 As Variant Dim n As Long, i As Long v1 = r1 v2 = r2 v3 = r3 n = UBound(v1, 2) If UBound(v1, 1) < 1 Or UBound(v1, 1) < 1 Or n < UBound(v2, 2) Then combineRows2 = CVErr(xlErrValue) Else ReDim v(1 To 3, 1 To n) For i = 1 To n v(1, i) = v1(1, i) v(2, i) = v2(1, i) v(3, i) = v3(1, i) Next combineRows2 = v End If End Function But, I dont understand why this part of the function repeat twice: UBound(v1, 1) < 1 Or UBound(v1, 1) < 1 Well, like I say you help me a lot, thanks for all. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LINEST multiple known_x's separated arrays
Sorry for the previous posting. Fat-finger missend.
"Rickemil" wrote: joeu2004 escribió: v1 = r1 v2 = r2 n = UBound(v1, 2) If UBound(v1, 1) < 1 Or UBound(v1, 1) < 1 Or n < UBound(v2, 2) Then [....] But, I dont understand why this part of the function repeat twice: UBound(v1, 1) < 1 Or UBound(v1, 1) < 1 Typo. The line should be: If UBound(v1, 1) < 1 Or UBound(v2, 1) < 1 Or n < UBound(v2, 2) Then "Rickemil" wrote: v1 = r1 v2 = r2 v3 = r3 n = UBound(v1, 2) If UBound(v1, 1) < 1 Or UBound(v1, 1) < 1 Or n < UBound(v2, 2) Then For completeness, the test should be: If UBound(v1, 1) < 1 Or UBound(v2, 1) < 1 Or UBound(v3, 1) < 1 _ Or n < UBound(v2, 2) Or n < UBound(v3, 2) Then That determines if r1, r2 and r3 are 1-row horizontal ranges, and all ranges are the same number of columns (n). |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LINEST multiple known_x's separated arrays
Thank you very much.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LinEst using arrays | Excel Programming | |||
Building Non-Contiguous Arrays For Use With Linest | Excel Worksheet Functions | |||
linest won't accept multiple reference ranges | Excel Worksheet Functions | |||
using multiple split cell references in linest | Excel Worksheet Functions | |||
How to use linest with variably sized data arrays? | Excel Worksheet Functions |