Average a range within an array
 

I have a 2D array called PeriodicArray. It is 30 columns wide and 10 rows
deep. I want to average all the data in the first column of the array. Why
is the code below not working?

PeriodicAvg =
Application.WorksheetFunction.Average(.WorksheetFu nction.Offset(PeriodicArray, 0, 0, 10, 1))

Thanks

EM

Average a range within an array
 

Hi Excel Monkey,

Perhaps try something like:

'=============
Public Sub Tester()
Dim rng As Range
Dim PeriodicAvg As Double

Set rng = Range("PeriodicArray")

PeriodicAvg = Application.Average(rng.Columns(1))

MsgBox PeriodicAvg

End Sub
'<<=============


---
Regards,
Norman


"ExcelMonkey" wrote in message
...
I have a 2D array called PeriodicArray. It is 30 columns wide and 10 rows
deep. I want to average all the data in the first column of the array.
Why
is the code below not working?

PeriodicAvg =
Application.WorksheetFunction.Average(.WorksheetFu nction.Offset(PeriodicArray,
0, 0, 10, 1))

Thanks

EM

Average a range within an array
 

Sorry Norman. My data is in an VBA array. I want to extract the Average of
Column 1 within that VBA Array. I think your answer assumed the data was in
a range in the spreadsheet.

EM

"Norman Jones" wrote:

Hi Excel Monkey,

Perhaps try something like:

'=============
Public Sub Tester()
Dim rng As Range
Dim PeriodicAvg As Double

Set rng = Range("PeriodicArray")

PeriodicAvg = Application.Average(rng.Columns(1))

MsgBox PeriodicAvg

End Sub
'<<=============


---
Regards,
Norman


"ExcelMonkey" wrote in message
...
I have a 2D array called PeriodicArray. It is 30 columns wide and 10 rows
deep. I want to average all the data in the first column of the array.
Why
is the code below not working?

PeriodicAvg =
Application.WorksheetFunction.Average(.WorksheetFu nction.Offset(PeriodicArray,
0, 0, 10, 1))

Thanks

EM

Average a range within an array
 

Hi Excel Monkey,

Perhaps nearer to your intention try:

'=============
Public Sub Tester2()
Dim PeriodicArray As Variant
Dim PeriodicAvg As Double
Dim arr As Variant
Dim i As Long

'Load array, e.g.:
PeriodicArray = Range("A1").Resize(10, 30).Value

arr = PeriodicArray

For i = UBound(arr, 2) To 1 Step -1
ReDim Preserve arr(1 To 10, 1 To i)
Next i

PeriodicAvg = Application.Average(arr)

MsgBox PeriodicAvg

End Sub
'<<=============


---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi Excel Monkey,

Perhaps try something like:

'=============
Public Sub Tester()
Dim rng As Range
Dim PeriodicAvg As Double

Set rng = Range("PeriodicArray")

PeriodicAvg = Application.Average(rng.Columns(1))

MsgBox PeriodicAvg

End Sub
'<<=============


---
Regards,
Norman

Average a range within an array
 

Seems to work. Is it not possible to take the average of the column items
without looping? From what I can see you have transferred all the data from
the first column in the original array, to a new 1-D array and used the
function on that new array.

EM

"Norman Jones" wrote:

Hi Excel Monkey,

Perhaps nearer to your intention try:

'=============
Public Sub Tester2()
Dim PeriodicArray As Variant
Dim PeriodicAvg As Double
Dim arr As Variant
Dim i As Long

'Load array, e.g.:
PeriodicArray = Range("A1").Resize(10, 30).Value

arr = PeriodicArray

For i = UBound(arr, 2) To 1 Step -1
ReDim Preserve arr(1 To 10, 1 To i)
Next i

PeriodicAvg = Application.Average(arr)

MsgBox PeriodicAvg

End Sub
'<<=============


---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi Excel Monkey,

Perhaps try something like:

'=============
Public Sub Tester()
Dim rng As Range
Dim PeriodicAvg As Double

Set rng = Range("PeriodicArray")

PeriodicAvg = Application.Average(rng.Columns(1))

MsgBox PeriodicAvg

End Sub
'<<=============


---
Regards,
Norman

Average a range within an array
 

Hi Excel Monkey,

Sorry Norman. My data is in an VBA array. I want to extract the
Average of Column 1 within that VBA Array. I think your answer
assumed the data was in a range in the spreadsheet.

Your assumption is correct and , therefore, se my follow up post.

---
Regards,
Norman

Average a range within an array
 

Seems to work. Is it not possible to take the average of the column items
without looping?
Hi Excel Monkey,

Seems to work. Is it not possible to take the average of the column items
without looping?

Not AFAIK. Of course, you could use an external function to perform the
work. In this connection, Alan Beban has a range of Array functions which
may be of interest and which may be downloaded at:

http://home.pacbell.net/beban/

From what I can see you have transferred all the data from
the first column in the original array, to a new 1-D array and used the
function on that new array.

I created the new array simply to allow subsequent processing of the
original array. Obviously, if no such processing is required, the same
results could be obtained using only the primary array..


---
Regards,
Norman

Average a range within an array
 

Sorry one last question. It my original example, I wanted to average the
data in column 1 of the array. But what I will eventually want to do is to
average each column (i.e. all 30). So transfer data from first array to new
array, redimension it so that it only includes data from the column I am
interested in, then average it and then start again with second column etc.
In my example, I have 30 columns. I need to wrap another loop around your
code so that the average function is applied to "arr" 30 times based on each
column. Its not clear to me where I would do this in your code.

EM

"Norman Jones" wrote:

Hi Excel Monkey,

Perhaps nearer to your intention try:

'=============
Public Sub Tester2()
Dim PeriodicArray As Variant
Dim PeriodicAvg As Double
Dim arr As Variant
Dim i As Long

'Load array, e.g.:
PeriodicArray = Range("A1").Resize(10, 30).Value

arr = PeriodicArray

For i = UBound(arr, 2) To 1 Step -1
ReDim Preserve arr(1 To 10, 1 To i)
Next i

PeriodicAvg = Application.Average(arr)

MsgBox PeriodicAvg

End Sub
'<<=============


---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi Excel Monkey,

Perhaps try something like:

'=============
Public Sub Tester()
Dim rng As Range
Dim PeriodicAvg As Double

Set rng = Range("PeriodicArray")

PeriodicAvg = Application.Average(rng.Columns(1))

MsgBox PeriodicAvg

End Sub
'<<=============


---
Regards,
Norman

Average a range within an array
 

Norman Jones wrote:
Seems to work. Is it not possible to take the average of the column
items
without looping?

Not AFAIK. Of course, you could use an external function to perform the
work. In this connection, Alan Beban has a range of Array functions
which
may be of interest and which may be downloaded at:

http://home.pacbell.net/beban/
. . .
Regards,
Norman


Thanks for the plug, Norman, but

Application.Average(Application.Index(PeriodicArra y, 0, n))

will return the average of the values in the nth column.

Alan Beban