How to get sum of n cell above using ARRAYFORMULA-CodePudding

I want to calculate Relative Strength Index that use the sum of 14 day of Gains. I can use SUM function then drag down to the bottom, but I want it to expand infinitely (H16 to H). Is there any way to use ARRAYFORMULA? or I must use appscript? I try many things and can't figure the way out.

CodePudding user response：

try:

=INDEX(IF(SEQUENCE(MATCH(9, 1/(G3:G<>"")))<16,,
 MMULT(N(IFERROR(SPLIT(REGEXEXTRACT(" "&trim(FLATTEN(QUERY(TRANSPOSE(IF(
 SEQUENCE(MATCH(9, 1/(G3:G<>"")))>=SEQUENCE(1, MATCH(9, 1/(G3:G<>""))),
 TRANSPOSE(INDIRECT("G3:G"&MATCH(9, 1/(G:G<>"")))), )),,9^9))),
 REPT(" -?\d (?:\.\d )?", 14)&"$"), " "))), SEQUENCE(14, 1, 1, 0))))

CodePudding user response：

try:

=ARRAYFORMULA(IF(SEQUENCE(COUNTA(G3:G))<14,,
 MMULT(IFERROR(VLOOKUP(ROW(G3:G)-SEQUENCE(1, 14, 0),
 {ROW(G3:G), G3:G}, 2, )*1, 0), SEQUENCE(14, 1, 1, 0))))