Array Constants in Excel

Question

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!

Answer 1

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.

Answer 2

This short VBA UDF should do the job.

Public Function ToArray(rngCell As Range) As Variant

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

Answer 3

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)