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

Thanks

EM

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" <ExcelMonkey@discussions.microsoft.com> wrote in message
news:C38A5D42-1AA4-4136-8453-56ADB77ECBE3@microsoft.com...
>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

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" <normanjones@whereforartthou.com> wrote in message
news:%23cWCmusjGHA.4816@TK2MSFTNGP04.phx.gbl...
> 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

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" <ExcelMonkey@discussions.microsoft.com> wrote in message
> news:C38A5D42-1AA4-4136-8453-56ADB77ECBE3@microsoft.com...
> >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
>
>
>

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" <normanjones@whereforartthou.com> wrote in message
> news:%23cWCmusjGHA.4816@TK2MSFTNGP04.phx.gbl...
> > 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
>
>
>

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

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" <normanjones@whereforartthou.com> wrote in message
> news:%23cWCmusjGHA.4816@TK2MSFTNGP04.phx.gbl...
> > 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
>
>
>

> 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

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

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

Alan Beban