0

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.

  • Sorry I don't think what I am trying to achieve is as simple as that. – jiminssy Jul 27 at 21:26
0

=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


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.