CODEWITHSUNDEEP
excel-2010
Kay Dee
Kay Dee

Reputation: 31

How do I do this in excel 2010, sum of function array?

I want to do this: SUM(Function(A1:A6)). Currently only SUM(Function(A1),Function(A2), ... Function(A6)) seems do-able. Please help!

Very specifically I have a macro function TD(x,y), where x and y are single variables Now I want to do SUM(TD(x1:x10,y))

Upvotes: 3

Views: 1852

Answers (1)

mellamokb
mellamokb

Reputation: 56769

You can enter the formula using your first version if you hit CTRL+SHIFT+ENTER in Excel 2010. This will make it an array based formula. For instance, this should work fine:

=SUM(A1:A6^2)

EDIT: To use a user-defined function, you have to modify your function to take an array parameter. When you press CTRL+SHIFT+ENTER, it treats all ranges as parameters directly into your function and expects all functions applied to take range parameters and return an array result. Here is an example with a hypothetical function TD(x, y) = x + y that just returns the sum of the two parameters:

Function TD(ByVal x As Integer, ByVal y As Integer) As Integer
    TD = x + y
End Function

Function TD is the base function that we are defining as explained above.

Function TDArray(ByVal x As Variant, ByVal y As Variant) As Integer()
    Dim xc() As Integer
    Dim yc() As Integer
    Dim count As Integer

    If TypeName(x) = "Range" Then
        xc = GetRangeValues(x)
        count = UBound(xc)
    End If

    If TypeName(y) = "Range" Then
        yc = GetRangeValues(y)
        count = UBound(yc)
    End If

    Dim i As Integer
    Dim xVal As Integer
    Dim yVal As Integer
    Dim result() As Integer
    ReDim result(1 To count)
    For i = 1 To count
        If TypeName(x) = "Range" Then xVal = xc(i) Else xVal = x
        If TypeName(y) = "Range" Then yVal = yc(i) Else yVal = y
        result(i) = TD(xVal, yVal)
    Next

    TDArray = result
End Function

Function TDArray is a function that mirrors the functionality of TD, but is also able to take an array as a parameter. It works by accepting Variant parameters and then checking to see if a Range or an integer was passed. If a Range was passed, it is converted into an array, and the function TD is applied to each element in the array. The result then is returned as an array to the next function, which in this example would be the call to SUM.

Function GetRangeValues(ByVal r As Range) As Integer()
    Dim c As Range
    Dim result() As Integer
    Dim i As Integer
    ReDim result(1 To r.Cells.count)

    i = 1
    For Each c In r
        result(i) = c.Value
        i = i + 1
    Next
    GetRangeValues = result
End Function

Function GetRangeValues is a simple helper function that takes a Range parameter and converts it into an array which is easier to navigate because it can be iterated by index. Armed with these function definitions, it would now be possible to enter the following function into a cell, and press CTRL+SHIFT+ENTER, and get the expected result (note we use the array form TDArray - if you are applying the calculation to a pair of parameters, say TD(A4,A5), then you use TD; for any ranges, you must use TDArray):

=SUM(TDArray(X1:X10,y))

Hope this helps!

Upvotes: 1

Related Questions