Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using function with Arrays
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using function with Arrays
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 )) -- Regards, Sébastien <http://www.ondemandanalysis.com "matelot" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using function with Arrays
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 "matelot" wrote in message... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using function with Arrays
Guys,
Thanks for the quick response. Either way, it works beautfully. Mat "Jim Cone" wrote: 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 "matelot" wrote in message... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MATCH Function - 2 Dimension Arrays | Excel Worksheet Functions | |||
Using arrays or range in IF function - HELP PLS | Excel Worksheet Functions | |||
Arrays and Join Function | Excel Programming | |||
Help with a UDF Function using Arrays... | Excel Programming | |||
Multiple arrays in Median function -- VBA | Excel Programming |