using arrays to complete calcs
 

All,

I am trying to use a column of my 2-dimensional array in a worksheet
function. I unfortunately do not know how to do this. This is what I have
so far below (cut from certain portions). I am trying to use the
correllation function to compare the column data of arrTop to another column
data of arrTop. I'm thinking of making the columns I'm comparing ranges
first, then putting them in the correl function?

Thanks for any help,
Matt


ReDim arrTop(1 To LastRow, 1 To 11)
arrTop = ActiveCell.Offset(-ActiveCell.Row + 1, -12).Range("B" &
StartingRow & ":L" & EndingRow).Value

For j = 1 To 10
For k = 2 To 10
ActiveCell.Offset(j, k).Value =
Application.WorksheetFunction.Correl(arrTop(, j), arrTop(, k))
Next k
Next j

using arrays to complete calcs
 

Dana DeLouis's response to your "simplify" post on 04/17/2009 answers this question too.
--
Jim Cone
Portland, Oregon USA




Matt S"

wrote in message
All,
I am trying to use a column of my 2-dimensional array in a worksheet
function. I unfortunately do not know how to do this. This is what I have
so far below (cut from certain portions). I am trying to use the
correllation function to compare the column data of arrTop to another column
data of arrTop. I'm thinking of making the columns I'm comparing ranges
first, then putting them in the correl function?
Thanks for any help,
Matt

ReDim arrTop(1 To LastRow, 1 To 11)
arrTop = ActiveCell.Offset(-ActiveCell.Row + 1, -12).Range("B" &
StartingRow & ":L" & EndingRow).Value

For j = 1 To 10
For k = 2 To 10
ActiveCell.Offset(j, k).Value =
Application.WorksheetFunction.Correl(arrTop(, j), arrTop(, k))
Next k
Next j

using arrays to complete calcs
 

So Jim,

I tried to apply what Dana used below. Is it correct?

Thanks,
Matt

For j = 1 To 9
For k = 1 To 9
With Application.WorksheetFunction
ActiveCell.Offset(j + 1, k).Value =
..Correl(.Index(arrTop, 0, k), .Index(arrTop, 0, j))
End With
Next k
Next j

It doesn't seem to give the same values if I were to do it by hand.

Thanks,
Matt

"Jim Cone" wrote:


Dana DeLouis's response to your "simplify" post on 04/17/2009 answers this question too.
--
Jim Cone
Portland, Oregon USA




Matt S"

wrote in message
All,
I am trying to use a column of my 2-dimensional array in a worksheet
function. I unfortunately do not know how to do this. This is what I have
so far below (cut from certain portions). I am trying to use the
correllation function to compare the column data of arrTop to another column
data of arrTop. I'm thinking of making the columns I'm comparing ranges
first, then putting them in the correl function?
Thanks for any help,
Matt

ReDim arrTop(1 To LastRow, 1 To 11)
arrTop = ActiveCell.Offset(-ActiveCell.Row + 1, -12).Range("B" &
StartingRow & ":L" & EndingRow).Value

For j = 1 To 10
For k = 2 To 10
ActiveCell.Offset(j, k).Value =
Application.WorksheetFunction.Correl(arrTop(, j), arrTop(, k))
Next k
Next j

using arrays to complete calcs
 

ah nevermind... I am getting the same thing! GREAT THANK YOU!

"Matt S" wrote:

So Jim,

I tried to apply what Dana used below. Is it correct?

Thanks,
Matt

For j = 1 To 9
For k = 1 To 9
With Application.WorksheetFunction
ActiveCell.Offset(j + 1, k).Value =
.Correl(.Index(arrTop, 0, k), .Index(arrTop, 0, j))
End With
Next k
Next j

It doesn't seem to give the same values if I were to do it by hand.

Thanks,
Matt

"Jim Cone" wrote:


Dana DeLouis's response to your "simplify" post on 04/17/2009 answers this question too.
--
Jim Cone
Portland, Oregon USA




Matt S"

wrote in message
All,
I am trying to use a column of my 2-dimensional array in a worksheet
function. I unfortunately do not know how to do this. This is what I have
so far below (cut from certain portions). I am trying to use the
correllation function to compare the column data of arrTop to another column
data of arrTop. I'm thinking of making the columns I'm comparing ranges
first, then putting them in the correl function?
Thanks for any help,
Matt

ReDim arrTop(1 To LastRow, 1 To 11)
arrTop = ActiveCell.Offset(-ActiveCell.Row + 1, -12).Range("B" &
StartingRow & ":L" & EndingRow).Value

For j = 1 To 10
For k = 2 To 10
ActiveCell.Offset(j, k).Value =
Application.WorksheetFunction.Correl(arrTop(, j), arrTop(, k))
Next k
Next j

using arrays to complete calcs
 

Hi Matt,

try this:

with Application.WorksheetFunction
ActiveCell.Offset(j, k).Value = .Correl(.Index(arrTop, 0, j),
..Index(arrTop, 0, k))
End With

Not tested tough...

Hope this helps,
Dominik.



Matt S schrieb:
All,

I am trying to use a column of my 2-dimensional array in a worksheet
function. I unfortunately do not know how to do this. This is what I have
so far below (cut from certain portions). I am trying to use the
correllation function to compare the column data of arrTop to another column
data of arrTop. I'm thinking of making the columns I'm comparing ranges
first, then putting them in the correl function?

Thanks for any help,
Matt


ReDim arrTop(1 To LastRow, 1 To 11)
arrTop = ActiveCell.Offset(-ActiveCell.Row + 1, -12).Range("B" &
StartingRow & ":L" & EndingRow).Value

For j = 1 To 10
For k = 2 To 10
ActiveCell.Offset(j, k).Value =
Application.WorksheetFunction.Correl(arrTop(, j), arrTop(, k))
Next k
Next j