worksheetfunction.average

Hi,

I have next issue with this code

Sub test_array_average()

Dim avgtime As Date
Dim avg As Date

t = Array("11:00:00", "11:00:06")
avgtime = WorksheetFunction.Average(t(0), t(1))
avg = WorksheetFunction.Average(t)

End Sub

It calculates avgtime correct but when it starts with the next line the program returns error 1004
The only difference is that t is an array with the same time values as above.
Somebody can help me to solve this probem?

Thanks in advance

Hello tifr,

Welcome to the Forum,

Moderation removed. Code Tags added.

I don't know for sure why this is working this way, but here's my guess -- Excel's average function is designed to ignore any "non-numeric" data. The way you have t defined, t is an array of variant/strings. When you reference each element separately, VBA decides to convert the individual elements to a suitable numeric type for the AVERAGE() function -- meaning converting the strings to dates to doubles. When you reference the array, then the array is passed to the AVERAGE() function as an array of strings, and the average function ignores the strings. The error comes when the AVERAGE() function cannot find any numbers in the array, and it can't divide by n=0.

I used this version of your sub to test further

Sub test_array_average()

Dim avgtime As Date
Dim avg As Date
three different definitions for t. uncomment the one you want to test
't = Array("11:00:00", "11:00:06")
't = Array(#11:00:00 AM#, #11:00:06 AM#)
t = Array(CDbl(#11:00:00 AM#), CDbl(#11:00:06 AM#))
'Stop statement pauses the procedure and enters debug mode
Stop
avgtime = WorksheetFunction.Average(t(0), t(1))
avg = WorksheetFunction.Average(t)
End Sub

Note that I have three different ways to define t. The first one is the one you started with -- t is an array of variant/strings. The second one is an array of variant/dates. The last one is an array of variant/doubles. All three variations work with the first average function (referencing individual elements of the array), but only the last works for the second average function (referencing t as an array of variant/double). Additional testing suggests that it would also work with other numeric data types (like integer, decimal).

It appears to me to be an issue with data types. I don't know how to further test/debug this without knowing a lot more (maybe the proprietary stuff that MS is not going to tell us) about the internal workings of the AVERAGE() function and how it decides which data types to include and which to ignore. However, the end solution seems relatively simple -- using suitable type conversion functions (https://msdn.microsoft.com/en-us/vba...sion-functions ) to coerce the elements of t into numeric data types that the AVERAGE() function will recognize as numbers.

Only this one worked:

Sub test_array_average()

Dim avgtime As Date, avg As Date, t

t = Array(CDbl(#11:00:00 AM#), CDbl(#11:00:06 AM#)) 'Mr Shorty
avgtime = WorksheetFunction.Average(t(0), t(1))
avg = WorksheetFunction.Average(t)

End Sub