Link: https://docs.google.com/spreadsheets/d/1spA9Y7ViVvQr7JSWretNcC97w1ozdZRqOKEnvBQIom4/edit?usp=sharing

Hi, I was wondering how to transform the following formula to an array formula:

=SUM(SEQUENCE(ROW()))

Goal:

• No helper column
• Arrayformula
• No scripts

If dragged over 5 rows, it creates cumulative sum:

1
3
6
10
15

(see B1:B5 in attached link)

Unfortunately, this solution does not work:

=ARRAYFORMULA(SUM(SEQUENCE(ROW(A1:A5))))

Why? Sequence requires integer as the first argument and ROW(A1:A5), if placed in an outer ARRAYFORMULA, behaves as an array. Sequence than takes treats this as:

=ARRAYFORMULA(SUM(SEQUENCE(ROW(A1))))

…resulting in just one cell filled with number 5 (SUM(1,1,1,1,1))

(see A1 in attached link)

I basically need to keep the outer ARRAYFORMULA, while “de-arraying” the ROW() formula.
Any ideas?

This is just a simplified version of the formula that I created for the purpose of posting here.

My original formula that I’d like to transform to an array formula:

=IF(
AND(
ROW() – 1 <= ROUNDDOWN($Q$4 * 12, 0),
$C$1 = TRUE
),
LAMBDA(
var,
IF(
$P$17 = “Ano”,
(1 – 0.15) * var + 0.15 * ((ROW() – 1) * ($Q$10 + $Q$28 + $Q$29) + ROUNDDOWN((ROW() – 1) / 12, 0) * $P$30),
var
)
)(
($Q$10 + $Q$28 + $Q$29) * SUMPRODUCT(($Q$7 / 100 + 1) ^ SEQUENCE(ROW() – 1, 1, 1, 1)) + ROUNDDOWN((ROW() – 1) / 12, 0) * $P$30
),
“”
)

Thanks!