Array Formulas for Personal Functions
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Array Formulas for Personal Functions

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

    Default

     
    Can the entry of array formulas in cells work with your own self-written VBA functions?

    I wrote a function that recieves a Range,
    but when I try to utilize it with a conditional array,
    example:
    {=MYFUNCTION(IF(A1:A100=10,B1:B100))}

    I get #VALUE. Am I doing something wrong, or is this just not allowed with homemade functions? I did press CTRL+Shift+Enter BTW.

  2. #2
    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

    Post your function and what you hope to accomplish with it and I'm sure that someone will be able to help you.

    -rh

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

    Default

      
    On 2002-03-07 16:00, Russell Hauf wrote:
    Post your function and what you hope to accomplish with it and I'm sure that someone will be able to help you.

    -rh
    I'm just expirementing, so my code is just
    an average function:

    ----
    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 with 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



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