Return SUM of values as an ARRAY

If values in A1 to A4:

=SUBTOTAL(9,OFFSET(A1,,,SEQUENCE(4)))

or: =SUBTOTAL(9,OFFSET(A1,,,ROW(A1:A4))) - may have to be entered with Ctrl+Shift+Enter, depending on your Excel version.

Awesome - this worked exactly how I needed it. Thanks!

Hi again,

I know this is marked as resolved, but I have one additional question, with those original perpetual growth % and a cash value, can I return an spill array containing each step of the FVSCHEDULE?

Please Login or Register  to view this content.

Again thank you for your help!

I don't know if FVSCHEDULE can spill and to be honest, in over 13 years, I've never had to used that function once despite working in the finance industry.

You can do the following in cells A1 to A4: 1.03 / 1.02 / 1.01 / 1.02 and then get your desired results:

=10000*SUBTOTAL(6,OFFSET(A1,,,ROW(A1:A4)))

And I'm not even sure if even that simple "1+" calculation could be incorporated in the formula.

See the file

Originally Posted by RaulSerg

I don't know if FVSCHEDULE can spill and to be honest, in over 13 years, I've never had to used that function once despite working in the finance industry.

You can do the following in cells A1 to A4: 1.03 / 1.02 / 1.01 / 1.02 and then get your desired results:

=10000*SUBTOTAL(6,OFFSET(A1,,,ROW(A1:A4)))

And I'm not even sure if even that simple "1+" calculation could be incorporated in the formula.

This is awesome - exactly what I needed. I had to add back in the SEQUENCE for the SPILL to work but that was all.