Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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.
=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
AFTER
