CODEWITHSUNDEEP
google-sheetssumgoogle-sheets-formulaarray-formulasgoogle-sheets-query
Digital Farmer
Digital Farmer

Reputation: 2147

Arrayformula + SUM + INDIRECT

I am trying to get the spreadsheet to use the value of L2 as a reference to calculate the cash value at the moment:

enter image description here

From the formula:

=arrayformula(IF(I2:I<>"",$L$2+SUM(INDIRECT("G2:G"&I2:I)),""))

But the lines below always return the same result, in this case 100.

I wonder where I am going wrong in creating this formula

Another detail:

I have made a chart ready to treat this cash growth over time, if you decide to help by looking straight at the spreadsheet, by re-adjusting the correct formula, please also take a look if the chart is working correctly according to the data and the movement of values, always the last row of columns G and H at the beginning of the graph

This is the expected result in Bank Column: Capture the initial bankroll L2 and from it add the sequence of results achieved in each investment in COLUMN G

enter image description here

Link to spreadsheet

Upvotes: 1

Views: 315

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(QUERY(L2+MMULT(ARRAY_CONSTRAIN(SPLIT(REPT("0×", 
 ROW(INDIRECT("A1:A"&COUNTA(D2:D)+1))-1)&
 TEXTJOIN("×", 1, INDEX(SORT({INDIRECT("G2:G"&COUNTA(D2:D)+1), 
 ROW(INDIRECT("G2:G"&COUNTA(D2:D)+1))}, 2, 0),,1)), "×"), 999^99, 
 COUNTA(D2:D)+1)*1, ROW(INDIRECT("G1:G"&COUNTA(D2:D)+1))^0), 
 "offset 1", 0))

0

Upvotes: 1

Related Questions