Using function with Arrays

[Guest]

I have 2 columns where I read into an array and I want to be able to
calculate the average of column 1 and column 2 separately. Is there a way to
do that in Excel? the current solution is to do a loop from 2 dim to 1 dim.
But I am sure Excel has a faster way to read the array. Thanks for your
comments.

arr = Range("A1:B5").value
avg1= application.worksheetfunction.average(arr(1)) <-refer to column 1
avg2 = application.worksheetfunction.average(arr(2)) <- refer to column 2

 

[Guest]

Hi,
What about:
Dim Rg as range, Avg1 as double, avg2 as double
Set rg = Range("A1:B5")
Avg1= Application.WorksheetFunction.Average(rg.Columns(1))
Avg2= Application.WorksheetFunction.Average(rg.Columns(2))

 

[Jim Cone]

Run your own test to determine if it is faster, I didn't.
'-------------------------------
Sub TestForAverage()
Dim arr As Variant
Dim arr1 As Variant
Dim arr2 As Variant
Dim dblAverageOne As Double
Dim dblAverageTwo As Double

arr = Range("A1:B5").Value
arr1 = Application.Index(arr, 0, 1) '1st column
arr2 = Application.Index(arr, 0, 2) '2nd column

dblAverageOne = Application.Average(arr1)
dblAverageTwo = Application.Average(arr2)

MsgBox dblAverageOne & " " & dblAverageTwo
End Sub
'-------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



I have 2 columns where I read into an array and I want to be able to
calculate the average of column 1 and column 2 separately. Is there a way to
do that in Excel? the current solution is to do a loop from 2 dim to 1 dim.
But I am sure Excel has a faster way to read the array. Thanks for your
comments.

arr = Range("A1:B5").value
avg1= application.worksheetfunction.average(arr(1)) <-refer to column 1
avg2 = application.worksheetfunction.average(arr(2)) <- refer to column 2

 

[Guest]

Guys,
Thanks for the quick response. Either way, it works beautfully.

Mat