Hello:

I have a routine that asks the user to select two ranges (using
application.input box). One of the ranges is a column vector rngY and
the second is a matrix or column vector rngX. The two regions are
required to have the same number of rows and the matrix must be
contiguous.

These two are passed to linest() function in Excel as:

Result = Application.WorksheetFunction.LinEst(rngY, rngX, True, True)

Everything works fine and I get the expected results.

Now I want to change the result so that only the first n rows are
used. Something like:

Result = Application.WorksheetFunction.LinEst(rngY.rows(1 to 5),
rngX.rows(1 to 5), True, True)

where the actual number of rows in rngY and rngX might be 10. ie I
want to use the first half the data to run the regression.


I know that for columns I can use:

Result = Application.WorksheetFunction.LinEst(rngY.Columns(1),
rngX.Columns(2), True, True)

Which will correlate the Y variable with 2nd X variable. I would also
like to be able to the same thing for multiple columns which is also I
problem I have not solved

I could something like (which would allow me to select any subset of
rows):

Startrow = 1
Endrow = 5
For i = 1 To col
For j = Startrow To endrow
Xstore(j, i) = rngX(j, i)
Next j
Next i
For j = Startrow To endrow
Ystore(j) - rngY(J)
next j

Then Xstore and Ystore would contain the right data but this creates
two problems, it is slow and the linest function expect ranges.

Can someone suggest a strategy? Thank you.

On Jul 25, 6:21*pm, Pieter <pieter.vandenb...@gmail.com> wrote:
Typos (sorry):

Change:
Which will correlate the Y variable with 2nd X variable. I would also
like to be able to the same thing for multiple columns which is also I
problem I have not solved
To:
Which will correlate the Y variable with 2nd X variable. I would also
like to be able to do the same thing for multiple columns which is
also a
problem I have not solved

Change
Ystore(j) - rngY(J)
to:
Ystore(j) = rngY(J)

Pieter


> Hello:
>
> I have a routine that asks the user to select two ranges (using
> application.input box). One of the ranges is a column vector rngY and
> the second is a matrix or *column vector rngX. The two regions are
> required to have the same number of rows and the matrix must be
> contiguous.
>
> These two are passed to linest() function in Excel as:
>
> *Result = Application.WorksheetFunction.LinEst(rngY, rngX, True, True)
>
> Everything works fine and I get the expected results.
>
> Now I want to change the result so that only the first n rows are
> used. Something like:
>
> Result = Application.WorksheetFunction.LinEst(rngY.rows(1 to 5),
> rngX.rows(1 to 5), True, True)
>
> where the actual number of rows in rngY and rngX might be 10. ie I
> want to use the first half the data to run the regression.
>
> I know that for columns I can use:
>
> Result = Application.WorksheetFunction.LinEst(rngY.Columns(1),
> rngX.Columns(2), True, True)
>
> Which will correlate the Y variable with 2nd X variable. I would also
> like to be able to the same thing for multiple columns which is also I
> problem I have not solved
>
> I could something like (which would allow me to select any subset of
> rows):
>
> Startrow = 1
> Endrow = 5
> For i = 1 To col
> For j = Startrow To endrow
> Xstore(j, i) = rngX(j, i)
> Next j
> Next i
> For j = Startrow To endrow
> Ystore(j) - rngY(J)
> next j
>
> Then Xstore and Ystore would contain the right data but this creates
> two problems, it is slow and the linest function expect ranges.
>
> Can someone suggest a strategy? Thank you.

I've not used the LinEst function before, so I'm guessing here, but it
sounds like what you want to use is the Resize property of the range. See if
this does what you want...

Result = Application.WorksheetFunction.LinEst(rngY.Resize(5),
rngX.Resize(5), True, True)

Now, that resize takes place from the first row. If instead of the first 5
rows, you wanted the middle 5 rows (from a group of 11 rows total for
example), then you would use the Resize property in conjunction with the
Offset property...

Result = Application.WorksheetFunction.LinEst(rngY.Offset(4).Resize(5),
rngX.Offset(4).Resize(5), True, True)

Check out the Resize and Offset properties (of the Range object) in the help
files to see about handling the columns via the optional 2nd argument for
them.

--
Rick (MVP - Excel)


"Pieter" <(E-Mail Removed)> wrote in message
news:ab3db682-2391-4b28-a179-(E-Mail Removed)...
On Jul 25, 6:21 pm, Pieter <pieter.vandenb...@gmail.com> wrote:
Typos (sorry):

Change:
Which will correlate the Y variable with 2nd X variable. I would also
like to be able to the same thing for multiple columns which is also I
problem I have not solved
To:
Which will correlate the Y variable with 2nd X variable. I would also
like to be able to do the same thing for multiple columns which is
also a
problem I have not solved

Change
Ystore(j) - rngY(J)
to:
Ystore(j) = rngY(J)

Pieter


> Hello:
>
> I have a routine that asks the user to select two ranges (using
> application.input box). One of the ranges is a column vector rngY and
> the second is a matrix or column vector rngX. The two regions are
> required to have the same number of rows and the matrix must be
> contiguous.
>
> These two are passed to linest() function in Excel as:
>
> Result = Application.WorksheetFunction.LinEst(rngY, rngX, True, True)
>
> Everything works fine and I get the expected results.
>
> Now I want to change the result so that only the first n rows are
> used. Something like:
>
> Result = Application.WorksheetFunction.LinEst(rngY.rows(1 to 5),
> rngX.rows(1 to 5), True, True)
>
> where the actual number of rows in rngY and rngX might be 10. ie I
> want to use the first half the data to run the regression.
>
> I know that for columns I can use:
>
> Result = Application.WorksheetFunction.LinEst(rngY.Columns(1),
> rngX.Columns(2), True, True)
>
> Which will correlate the Y variable with 2nd X variable. I would also
> like to be able to the same thing for multiple columns which is also I
> problem I have not solved
>
> I could something like (which would allow me to select any subset of
> rows):
>
> Startrow = 1
> Endrow = 5
> For i = 1 To col
> For j = Startrow To endrow
> Xstore(j, i) = rngX(j, i)
> Next j
> Next i
> For j = Startrow To endrow
> Ystore(j) - rngY(J)
> next j
>
> Then Xstore and Ystore would contain the right data but this creates
> two problems, it is slow and the linest function expect ranges.
>
> Can someone suggest a strategy? Thank you.

Hello Rick:

Your suggestion was exactly what I was looking for. It works
beautifully for the row problem. Thank you.

I have not tried the column solution yet. I think the reisze option
only works at the start or the end of a block of columns (or rows). I
need to be able to delete columns from the middle and have the range
"resized in the middle." For example if the range is rngX(A), I want
to be able to allow the user to remove a column, let's say C and end
up with the range rngX(A:C) which [I could then pass as rngX to the
function] now contains the data from columns A, B, and D just as Excel
does when you delete part of a column and it asks which way you want
to shift the cells. I will give it a try later, I am not quite at that
place in the project yet.

Again thanks for your response.

Pieter


On Jul 26, 8:59*am, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> I've not used the LinEst function before, so I'm guessing here, but it
> sounds like what you want to use is the Resize property of the range. Seeif
> this does what you want...
>
> Result = Application.WorksheetFunction.LinEst(rngY.Resize(5),
> rngX.Resize(5), True, True)
>
> Now, that resize takes place from the first row. If instead of the first 5
> rows, you wanted the middle 5 rows (from a group of 11 rows total for
> example), then you would use the Resize property in conjunction with the
> Offset property...
>
> Result = Application.WorksheetFunction.LinEst(rngY.Offset(4).Resize(5),
> rngX.Offset(4).Resize(5), True, True)
>
> Check out the Resize and Offset properties (of the Range object) in the help
> files to see about handling the columns via the optional 2nd argument for
> them.
>
> --
> Rick (MVP - Excel)
>
> "Pieter" <pieter.vandenb...@gmail.com> wrote in message
>
> news:ab3db682-2391-4b28-a179-(E-Mail Removed)...
> On Jul 25, 6:21 pm, Pieter <pieter.vandenb...@gmail.com> wrote:
> Typos (sorry):
>
> Change:
> Which will correlate the Y variable with 2nd X variable. I would also
> like to be able to the same thing for multiple columns which is also I
> problem I have not solved
> To:
> Which will correlate the Y variable with 2nd X variable. I would also
> like to be able to do the same thing for multiple columns which is
> also a
> problem I have not solved
>
> Change
> Ystore(j) - rngY(J)
> to:
> Ystore(j) = rngY(J)
>
> Pieter
>
> > Hello:
>
> > I have a routine that asks the user to select two ranges (using
> > application.input box). One of the ranges is a column vector rngY and
> > the second is a matrix or column vector rngX. The two regions are
> > required to have the same number of rows and the matrix must be
> > contiguous.
>
> > These two are passed to linest() function in Excel as:
>
> > Result = Application.WorksheetFunction.LinEst(rngY, rngX, True, True)
>
> > Everything works fine and I get the expected results.
>
> > Now I want to change the result so that only the first n rows are
> > used. Something like:
>
> > Result = Application.WorksheetFunction.LinEst(rngY.rows(1 to 5),
> > rngX.rows(1 to 5), True, True)
>
> > where the actual number of rows in rngY and rngX might be 10. ie I
> > want to use the first half the data to run the regression.
>
> > I know that for columns I can use:
>
> > Result = Application.WorksheetFunction.LinEst(rngY.Columns(1),
> > rngX.Columns(2), True, True)
>
> > Which will correlate the Y variable with 2nd X variable. I would also
> > like to be able to the same thing for multiple columns which is also I
> > problem I have not solved
>
> > I could something like (which would allow me to select any subset of
> > rows):
>
> > Startrow = 1
> > Endrow = 5
> > For i = 1 To col
> > For j = Startrow To endrow
> > Xstore(j, i) = rngX(j, i)
> > Next j
> > Next i
> > For j = Startrow To endrow
> > Ystore(j) - rngY(J)
> > next j
>
> > Then Xstore and Ystore would contain the right data but this creates
> > two problems, it is slow and the linest function expect ranges.
>
> > Can someone suggest a strategy? Thank you.

Re-read my posting.. I talk about using the Offset property to move the
starting point and then Resize after doing that (see the example I posted).

--
Rick (MVP - Excel)


"Pieter" <(E-Mail Removed)> wrote in message
news:d71ec538-a6f7-4d0f-9f3f-(E-Mail Removed)...
Hello Rick:

Your suggestion was exactly what I was looking for. It works
beautifully for the row problem. Thank you.

I have not tried the column solution yet. I think the reisze option
only works at the start or the end of a block of columns (or rows). I
need to be able to delete columns from the middle and have the range
"resized in the middle." For example if the range is rngX(A), I want
to be able to allow the user to remove a column, let's say C and end
up with the range rngX(A:C) which [I could then pass as rngX to the
function] now contains the data from columns A, B, and D just as Excel
does when you delete part of a column and it asks which way you want
to shift the cells. I will give it a try later, I am not quite at that
place in the project yet.

Again thanks for your response.

Pieter


On Jul 26, 8:59 am, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> I've not used the LinEst function before, so I'm guessing here, but it
> sounds like what you want to use is the Resize property of the range. See
> if
> this does what you want...
>
> Result = Application.WorksheetFunction.LinEst(rngY.Resize(5),
> rngX.Resize(5), True, True)
>
> Now, that resize takes place from the first row. If instead of the first 5
> rows, you wanted the middle 5 rows (from a group of 11 rows total for
> example), then you would use the Resize property in conjunction with the
> Offset property...
>
> Result = Application.WorksheetFunction.LinEst(rngY.Offset(4).Resize(5),
> rngX.Offset(4).Resize(5), True, True)
>
> Check out the Resize and Offset properties (of the Range object) in the
> help
> files to see about handling the columns via the optional 2nd argument for
> them.
>
> --
> Rick (MVP - Excel)
>
> "Pieter" <pieter.vandenb...@gmail.com> wrote in message
>
> news:ab3db682-2391-4b28-a179-(E-Mail Removed)...
> On Jul 25, 6:21 pm, Pieter <pieter.vandenb...@gmail.com> wrote:
> Typos (sorry):
>
> Change:
> Which will correlate the Y variable with 2nd X variable. I would also
> like to be able to the same thing for multiple columns which is also I
> problem I have not solved
> To:
> Which will correlate the Y variable with 2nd X variable. I would also
> like to be able to do the same thing for multiple columns which is
> also a
> problem I have not solved
>
> Change
> Ystore(j) - rngY(J)
> to:
> Ystore(j) = rngY(J)
>
> Pieter
>
> > Hello:
>
> > I have a routine that asks the user to select two ranges (using
> > application.input box). One of the ranges is a column vector rngY and
> > the second is a matrix or column vector rngX. The two regions are
> > required to have the same number of rows and the matrix must be
> > contiguous.
>
> > These two are passed to linest() function in Excel as:
>
> > Result = Application.WorksheetFunction.LinEst(rngY, rngX, True, True)
>
> > Everything works fine and I get the expected results.
>
> > Now I want to change the result so that only the first n rows are
> > used. Something like:
>
> > Result = Application.WorksheetFunction.LinEst(rngY.rows(1 to 5),
> > rngX.rows(1 to 5), True, True)
>
> > where the actual number of rows in rngY and rngX might be 10. ie I
> > want to use the first half the data to run the regression.
>
> > I know that for columns I can use:
>
> > Result = Application.WorksheetFunction.LinEst(rngY.Columns(1),
> > rngX.Columns(2), True, True)
>
> > Which will correlate the Y variable with 2nd X variable. I would also
> > like to be able to the same thing for multiple columns which is also I
> > problem I have not solved
>
> > I could something like (which would allow me to select any subset of
> > rows):
>
> > Startrow = 1
> > Endrow = 5
> > For i = 1 To col
> > For j = Startrow To endrow
> > Xstore(j, i) = rngX(j, i)
> > Next j
> > Next i
> > For j = Startrow To endrow
> > Ystore(j) - rngY(J)
> > next j
>
> > Then Xstore and Ystore would contain the right data but this creates
> > two problems, it is slow and the linest function expect ranges.
>
> > Can someone suggest a strategy? Thank you.

Hello:

I will work through it and see if I can understand what you are
suggesting with respect to columns. I really have not yet tackled it
so I have not worked through it or thought about the details yet. I
might post back if I can't work it out. Again thanks.

Pieter

On Jul 26, 5:50*pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> Re-read my posting.. I talk about using the Offset property to move the
> starting point and then Resize after doing that (see the example I posted).
>
> --
> Rick (MVP - Excel)
>
> "Pieter" <pieter.vandenb...@gmail.com> wrote in message
>
> news:d71ec538-a6f7-4d0f-9f3f-(E-Mail Removed)...
> Hello Rick:
>
> Your suggestion was exactly what I was looking for. It works
> beautifully for the row problem. Thank you.
>
> I have not tried the column solution yet. I think the reisze option
> only works at the start or the end of a block of columns (or rows). I
> need to be able to delete columns from the middle and have the range
> "resized in the middle." For example if the range is rngX(A), I want
> to be able to allow the user to remove a column, let's say C and end
> up with the range rngX(A:C) which [I could then pass as rngX to the
> function] now contains the data from columns A, B, and D just as Excel
> does when you delete part of a column and it asks which way you want
> to shift the cells. I will give it a try later, I am not quite at that
> place in the project yet.
>
> Again thanks for your response.
>
> Pieter
>
> On Jul 26, 8:59 am, "Rick Rothstein"
>
> <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> > I've not used the LinEst function before, so I'm guessing here, but it
> > sounds like what you want to use is the Resize property of the range. See
> > if
> > this does what you want...
>
> > Result = Application.WorksheetFunction.LinEst(rngY.Resize(5),
> > rngX.Resize(5), True, True)
>
> > Now, that resize takes place from the first row. If instead of the first 5
> > rows, you wanted the middle 5 rows (from a group of 11 rows total for
> > example), then you would use the Resize property in conjunction with the
> > Offset property...
>
> > Result = Application.WorksheetFunction.LinEst(rngY.Offset(4).Resize(5),
> > rngX.Offset(4).Resize(5), True, True)
>
> > Check out the Resize and Offset properties (of the Range object) in the
> > help
> > files to see about handling the columns via the optional 2nd argument for
> > them.
>
> > --
> > Rick (MVP - Excel)
>
> > "Pieter" <pieter.vandenb...@gmail.com> wrote in message
>
> >news:ab3db682-2391-4b28-a179-(E-Mail Removed)...
> > On Jul 25, 6:21 pm, Pieter <pieter.vandenb...@gmail.com> wrote:
> > Typos (sorry):
>
> > Change:
> > Which will correlate the Y variable with 2nd X variable. I would also
> > like to be able to the same thing for multiple columns which is also I
> > problem I have not solved
> > To:
> > Which will correlate the Y variable with 2nd X variable. I would also
> > like to be able to do the same thing for multiple columns which is
> > also a
> > problem I have not solved
>
> > Change
> > Ystore(j) - rngY(J)
> > to:
> > Ystore(j) = rngY(J)
>
> > Pieter
>
> > > Hello:
>
> > > I have a routine that asks the user toselecttwo ranges (using
> > > application.input box). One of the ranges is a column vector rngY and
> > > the second is a matrix or column vector rngX. The two regions are
> > > required to have the same number of rows and the matrix must be
> > > contiguous.
>
> > > These two are passed to linest() function in Excel as:
>
> > > Result = Application.WorksheetFunction.LinEst(rngY, rngX, True, True)
>
> > > Everything works fine and I get the expected results.
>
> > > Now I want to change the result so that only the first n rows are
> > > used. Something like:
>
> > > Result = Application.WorksheetFunction.LinEst(rngY.rows(1 to 5),
> > > rngX.rows(1 to 5), True, True)
>
> > > where the actual number of rows in rngY and rngX might be 10. ie I
> > > want to use the first half the data to run the regression.
>
> > > I know that for columns I can use:
>
> > > Result = Application.WorksheetFunction.LinEst(rngY.Columns(1),
> > > rngX.Columns(2), True, True)
>
> > > Which will correlate the Y variable with 2nd X variable. I would also
> > > like to be able to the same thing for multiple columns which is also I
> > > problem I have not solved
>
> > > I could something like (which would allow me toselectany subset of
> > > rows):
>
> > > Startrow = 1
> > > Endrow = 5
> > > For i = 1 To col
> > > For j = Startrow To endrow
> > > Xstore(j, i) = rngX(j, i)
> > > Next j
> > > Next i
> > > For j = Startrow To endrow
> > > Ystore(j) - rngY(J)
> > > next j
>
> > > Then Xstore and Ystore would contain the right data but this creates
> > > two problems, it is slow and the linest function expect ranges.
>
> > > Can someone suggest a strategy? Thank you.