Make array of numbers in one cell in Excel [closed]

Question

Is it possible to store some numbers in one cell (as an array)? Please see below:

cell A1: 4,4,3,4,1,3,3,4

cell A2: 5 (5th element of array)

cell A3: 546254 (constant for this month)

cell A4: =(MAX(A1)-INDEX(CHOOSE({1,2,3,4,5,6,7,8},A1),1,A2)) * (A3/SUM(A1)) (here 63029)

The formula of cell A4 causes error. Only way to solve it changing it as below:

cell A4: =(MAX(4,4,3,4,1,3,3,4)-INDEX(CHOOSE({1,2,3,4,5,6,7,8},4,4,3,4,1,3,3,4),1,A2)) * (A3/SUM(4,4,3,4,1,3,3,4))

So, I should write whole array in formula. I don't want to use VBA and Named Ranges.

Answer 1

EDIT:

With the new dynamic array formula TEXTSPLIT() we can shorten this:

=LET(arr,TEXTSPLIT(A1,","),MAX(arr)-INDEX(arr,,A1)*(A3/SUM(arr))

---

Original

A cell can not hold an array. It can hold a number or a string or an error. you will need to parse the string to create an array in the formula itself.

using FILTERXML to create an array from a string:

=(MAX(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s"))-FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s["&A2&"]"))  *  (A3/SUM(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s")))

If one does not have FILTERXML:

=(MAX(--TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),(ROW($ZZ1:INDEX($ZZ:$ZZ,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*99+1,99)))-INDEX(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),(ROW($ZZ1:INDEX($ZZ:$ZZ,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*99+1,99)),A2))  *  (A3/SUM(--TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),(ROW($ZZ1:INDEX($ZZ:$ZZ,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*99+1,99))))

This is an array formula and must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode