1

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.

BigBen
  • 46,229
  • 7
  • 24
  • 40
AliM67
  • 123
  • 12
  • 1
    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. – Scott Craner Sep 03 '20 at 16:21
  • 1
    do you have FILTERXML? that is one way to parse the string into an array. See: https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml – Scott Craner Sep 03 '20 at 16:25

1 Answers1

5

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

enter image description here

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

k

Scott Craner
  • 148,073
  • 10
  • 49
  • 81