Array constants in Excel

Question

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

I would like to get the formula SUM (A1) return 6. However, SUM uses A1 as a single-cell array, not the array constant contained within A1, and so SUM (A1) returns 1.

Similarly, I expect AVERAGE (A1) to return 1 instead of 2.

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

I do not want the array constant to be a named reference, because I define a different array constant for each row.

It seems to me that I'm stuck in C ++ without the possibility of dereferencing!

Answer 1

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 line with braces is in cell b2, for example, all you need to write in another cell is = sum (toarray (b2)))

Answer 2

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

Answer 3

, (: test) :

={1,2,3}

=SUM(test)