views:

355

answers:

3

I have an array constant defined in cell A1 as {1,2,3}. This displays as "1" (the first value in the array).

I would like to have the formula SUM(A1) return 6. However, SUM is using A1 as a single-celled array, rather than the array constant contained inside A1 - and therefore SUM(A1) returns 1.

Likewise, I would expect AVERAGE(A1) returns 1 instead of 2.

So simply speaking, how do I get SUM(A1) to return the same value as SUM({1,2,3})?

I don't want to make the array constant a named reference because i'm defining a different array constant for every row.

It feels like i'm stuck in C++ w/o a way to dereference!

A: 

You can make the array constant a named array constant, by defining a name (for example: test) like so:

={1,2,3}

then reference the name

=SUM(test)
Lance Roberts
I don't want to make the array constant a named reference because i'm defining a different array constant for every row.
ZaijiaN
+2  A: 

This is not the most elegant solution, but I believe it does what you want it to with a short VBA UDF.

Public Function ToArray(rngCell As Range) As Double()

    Dim sFormString As String
    sFormString = rngCell.Formula

    Dim adReturn() As Double
    ReDim adReturn(1) As Double
    If Not Len(sFormString) - 3 > 0 Then
        ToArray = adReturn
        Exit Function
    Else
        sFormString = Mid(sFormString, 3, Len(sFormString) - 3)
    End If

    Dim vTest As Variant
    vTest = Split(sFormString, ",")

    ReDim adReturn(LBound(vTest) To UBound(vTest)) As Double

    Dim iArrayCounter As Integer
    For iArrayCounter = LBound(vTest) To UBound(vTest)
        adReturn(iArrayCounter) = vTest(iArrayCounter)
    Next iArrayCounter

    ToArray = adReturn

End Function

(If the string with the curly brackets is in cell b2 for example, all you need to write in another cell is =sum(toarray(b2)) )

HKK
Could you modify this function to return the array? So it could be used in a formula like =SUM(TOARRAY(A1)) ?
ZaijiaN
This should be what you are looking for I hope
HKK
There's a couple of bugs in your example, but I was able to figure it out from there. Many thanks!1) declare adreturn before "If Not Len(sForm..."2) change "ArraySum = 0" to "ToArray = adreturn"
ZaijiaN
thanks for your comment, went a bit quickly :) , will make the necessary edits.
HKK
+1  A: 

A cell is limited to a single number, string, logical or error value. A single cell cannot contain an array. When the formula "={1,2,3}" is evaluated in a single cell formula, the cell will get only the first value from the array.

Joe Erickson