TransWikia.com

Calculate cumulative returns on Google Sheet with blank cells

Web Applications Asked by jiminssy on September 25, 2020

enter image description here
The formula that is in the Cumulative Return cell that is showing -0.09% is this.

=IF(ISBLANK(J2), 1, (1+J2))*IF(ISBLANK(J3), 1, (1+J3))*IF(ISBLANK(J4), 1, (1+J4))-1

How can I make it so that this automatically performs what I want when I insert new rows into Return. Right now I am having to keep adding IF(ISBLANK(J#), 1, (1+J#)) into the formula manually with incrementing # number.

One Answer

=PRODUCT(ARRAYFORMULA(IF(LEN(J2:J4),1+(J2:J4) ,1)))-1

The range is the key to the effectiveness of this formula.

When entering a new return/row, be careful to ensure that the formula range expands to include the new row. Don't enter a new row above the first row in the range, or below the last row in the range.

BEFORE

Before

AFTER After


Correct answer by Tedinoz on September 25, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP