Can You use Array formulas with user written functions?
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Can You use Array formulas with user written functions?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Take a look at the data types that you're passing as arguments to MyAverage...

    {1;4;FALSE;FALSE;10}

    ...One of the causes of the #VALUE! error are incompatible data types (see the last entry in the Help Topic for "What does the error #VALUE! mean?"). The AVERAGE worksheet function ignores boolean values -- that's the "trick" behind {=AVERAGE(IF(A1:A5=1,B1:B5))}. How does your UDF handle boolean values? Sorry, I can't answer this question for you. I'm not a VBA guru.

    BTW, {=AVERAGE(IF(A1:A5=1,B1:B5))} is 5 instead of 3. Perhaps, you were using a different data set.

    [ This Message was edited by: Mark W. on 2002-03-08 15:11 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, it's possible. The "trick" is that you need to declare your function as a Variant.

    Then, handle the info in a temporary array. But, if you need to do an AVERAGEIF function, I would go with Excel's built in way. Or you could just create an UDF that would "appear" as a SUMIF, but, really work as told.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried the variant approach, and it still doesnt work. I simplified the code greatly to JUST focus on getting a function to work with an array formula:
    ----
    Function MyAverage(DRange as Range)As Variant
    MyAverage = DRange.Range("A1").Value
    End Function
    ----

    In this attempt, I am only trying to assign the function with the value of the 1st cell in the range. It works if I dont enter the function in an array formula, but as an array formula, I still get "#VALUE!"

    I'm totally stumped. (I am pressing CTRL-SHIFT-ENTER)

  5. #5
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I was able to get it to work using the following code. Hope this helps!


    Option Explicit

    Function MyAverage(DRange As Variant) As Variant

    Dim R As Variant
    Dim Sum As Double
    Dim Count As Integer
    Dim intI As Integer
    Dim intJ As Integer

    For intI = 1 To UBound(DRange, 1)
    For intJ = 1 To UBound(DRange, 2)

    If TypeName(DRange(intI, intJ)) <> "Boolean" Then
    Sum = Sum + DRange(intI, intJ)
    Count = Count + 1
    End If

    Next intJ
    Next intI


    MyAverage = Sum / Count

    End Function



    [ This Message was edited by: Russell Hauf on 2002-03-09 15:23 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-09 14:18, John McGraw wrote:
    I tried the variant approach, and it still doesnt work. I simplified the code greatly to JUST focus on getting a function to work with an array formula:
    ----
    Function MyAverage(DRange as Range)As Variant
    MyAverage = DRange.Range("A1").Value
    End Function
    ----

    In this attempt, I am only trying to assign the function with the value of the 1st cell in the range. It works if I dont enter the function in an array formula, but as an array formula, I still get "#VALUE!"

    I'm totally stumped. (I am pressing CTRL-SHIFT-ENTER)
    This worked for me (With and Without CSE)


    Function MyAverage(DRange As Range) As Variant
    MyAverage = DRange.Range("A1")
    End Function




    But, i feel you need this:


    Function AverageIf(Range As Range, Criteria As String, Optional Average_Range As Range) As Variant
    Dim T As String
    If Not Evaluate("OR(" & Left(Criteria, 1) & "={""<"","">"",""=""})") Then _
    Criteria = "=" & Criteria
    If Average_Range Is Nothing Then
    AverageIf = Evaluate("AVERAGE(IF(" & Range.Address & Criteria & "," & Range.Address & "))")
    Else
    AverageIf = Evaluate("AVERAGE(IF(" & Range.Address & Criteria & "," & Average_Range.Address & "))")
    End If
    End Function



    Which works EXACTLY like SUMIF, no need to array enter.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Russell!

    I guess I am clueless as to how excel passes the array to the function. You are manipulating it as a 2 dimensional array, but to me it seems like it is 1 dimensional.

    In fact, with experimenting with your code I noticed that I could omit the "intj" inner loop and get the same result. (I replaced "DRange(intI,intJ)" with "DRange(IntI,1)") This tells me that the second dimension of the array only includes 1 field. But thats virtually a 1-dimensional array, right?

    As Mark W. mentioned, what is being passed to my function is {1;4;FALSE;FALSE;10}, or a 1d array. Why must it be manipulated as a 2d array?

    Anyways, your help was great. Thanks again!

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This worked for me (With and Without CSE)


    Function MyAverage(DRange As Range) As Variant
    MyAverage = DRange.Range("A1")
    End Function


    It did? I wonder why it didnt work for me?
    I changed ".Range("A1").Value" to simply
    ".Range("A1")" as you have above, and I still get the "#VALUE!" error when entered with CSE.

    Thanks for your help anyways!

    John

  9. #9
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    You are asking for calculation slow down by using arrays (try Excels Database functions instead) but combining these with UDF's is not advised at all. You see an array formula must loop through all cells referenced and see if the criteria matches (its a bit like uisng a loop in VBA to find a value when the Find Method would get there 100+ times quicker). When you also combine a loop with an array formula it could be doing the same thing twice.

    I would strongly advise using the DAVERAGE function for your needs. These functions are at least 100 times faster then array formulas, agian because they are not restricted to having to loop. If however you do use a UDF just add a condition to check, like:

    Function MyAverage(DRangeComp As Range, vExclude, dAveRange As Range) As Double
    Dim R As Range
    Dim vVal
    Dim iAveCount As Integer


    For Each R In DRangeComp
    If R <> vExclude Then
    vVal = WorksheetFunction.Sum(dAveRange.Cells(R.Row, 1), vVal)
    iAveCount = iAveCount + 1
    End If
    Next R

    MyAverage = vVal / iAveCount
    End Function

    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training
    If it's Excel, then it's us!

    [ This Message was edited by: Dave Hawley on 2002-03-09 20:26 ]

  10. #10
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-03-09 15:51, John McGraw wrote:
    Thanks Russell!

    I guess I am clueless as to how excel passes the array to the function. You are manipulating it as a 2 dimensional array, but to me it seems like it is 1 dimensional.

    In fact, with experimenting with your code I noticed that I could omit the "intj" inner loop and get the same result. (I replaced "DRange(intI,intJ)" with "DRange(IntI,1)") This tells me that the second dimension of the array only includes 1 field. But thats virtually a 1-dimensional array, right?

    As Mark W. mentioned, what is being passed to my function is {1;4;FALSE;FALSE;10}, or a 1d array. Why must it be manipulated as a 2d array?

    Anyways, your help was great. Thanks again!
    You can use it as a one-dimensional array for the range you selected, but in general, ranges you select in Excel are 2-dimensional. So if you select more than one column, you need the second dimension. Also, I suggest modifying the function to include only numeric types in the average (integer, long, single, double) - if you are going to continue along these lines, which you should not (this was just for you to try a UDF array function, correct?).

    Regards,

    Russell

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com