google sheets
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.
Adding a column for date and calculating the C values with:
=if(istext(D1),C2,D1+C2)
in D2 copied down a chart might be plotted of this Data Sheet1!A1:A4, Sheet1!D1:D4 with Use row 1 as headers:
The solution below involves a very elegant and generally useful technique popularized by ahab, a Google Docs expert; I suppose he invented it. It uses matrix multiplication.
Move the label "Starting" to the last cell of the Description column, column D.
Move the starting balance to the last cell of the Amount column, column E.
Insert a new blank row 2. This row will be reserved and should be left blank except for the following formula, so you might want to freeze it along with row 1.
Put the following formula in cell F2.
= ArrayFormula( if( isnumber(E2:E)
;
mmult(
transpose(E2:E) * (row(E2:E) <= transpose( row(E2:E) ) )
;
sign( row(E2:E) )
)
;
) )
Any row having a transaction amount in column E now has a corresponding running balance in column F. The running balance has the desired property that the initial balance is in the last row of column F and subsequent balances read up column F.
If, instead, you want your starting balance in the first (data) row of column F (cell F2) and subsequent balances to read down the column, change <= to >=.
Best Answer
=PRODUCT(ARRAYFORMULA(IF(LEN(J2:J4),1+(J2:J4) ,1)))-1The 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