John McGraw
Board Regular
- Joined
- Feb 25, 2002
- Messages
- 76
I would like to know if it is possible to get user written functions to work with array formulas.
As an example, here is a Average function I wrote to illustrate the question:
----
Function MyAverage(DRange As Range) As Double
Dim R As Object
Dim Sum As Double
Dim Count As Integer
Count = 0: Sum = 0
For Each R In DRange
Sum = Sum + R.Value
Count = Count + 1
Next R
MyAverage = Sum / Count
End Function
----
Now, this works fine if I just use it in normal formulas, but it returns "#VALUE!" if I try to use it in an array formula.
So, if column A = {1;1;2;2;1} and
B = {1;4;2;9;10}
I get the following results:
As Array Formulas
{=myaverage(IF(A1:A5=1,B1:B5))} = #VALUE!
{=AVERAGE(IF(A1:A5=1,B1:B5))} = 3
As Normal Formulas
=myaverage(B1:B5) = 5.2
=AVERAGE(B1:B5) = 5.2
How can I a user written function to work in an array formula? Or is it even possible?
Thanks for any help
John
As an example, here is a Average function I wrote to illustrate the question:
----
Function MyAverage(DRange As Range) As Double
Dim R As Object
Dim Sum As Double
Dim Count As Integer
Count = 0: Sum = 0
For Each R In DRange
Sum = Sum + R.Value
Count = Count + 1
Next R
MyAverage = Sum / Count
End Function
----
Now, this works fine if I just use it in normal formulas, but it returns "#VALUE!" if I try to use it in an array formula.
So, if column A = {1;1;2;2;1} and
B = {1;4;2;9;10}
I get the following results:
As Array Formulas
{=myaverage(IF(A1:A5=1,B1:B5))} = #VALUE!
{=AVERAGE(IF(A1:A5=1,B1:B5))} = 3
As Normal Formulas
=myaverage(B1:B5) = 5.2
=AVERAGE(B1:B5) = 5.2
How can I a user written function to work in an array formula? Or is it even possible?
Thanks for any help
John