Average a range within an array

[Guest]

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(.WorksheetFunction.Offset(PeriodicArray, 0, 0, 10, 1))

Thanks

EM

 

[Norman Jones]

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
'<<=============

 

[Guest]

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]

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
'<<=============

 

[Guest]

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

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
'<<=============

 

[Norman Jones]

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.

 

[Guest]

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

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
'<<=============

 

[Norman Jones]

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..

 

[Alan Beban]

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(PeriodicArray, 0, n))

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

Alan Beban