Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default LINEST multiple known_x's separated arrays

Thank you very much.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
LinEst using arrays REM[_2_] Excel Programming 4 October 17th 11 08:38 AM
Building Non-Contiguous Arrays For Use With Linest Marston Excel Worksheet Functions 4 May 19th 08 03:07 PM
linest won't accept multiple reference ranges Alex Gardner Excel Worksheet Functions 3 May 18th 05 01:20 PM
using multiple split cell references in linest Alex Excel Worksheet Functions 0 May 16th 05 05:37 PM
How to use linest with variably sized data arrays? [email protected] Excel Worksheet Functions 0 April 13th 05 04:56 PM


All times are GMT +1. The time now is 01:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"