I´m struggling with a formula in Google Sheets I want to implement. What I want to do is find the % change of the last 5 rows in a table. And everytime a new row gets added, that should again calculate the % change of this last "5 days block" cells.
So, as per the attached image, the current % change of the last 5 days should be 9.21% (9202/8425). But tomorrow when a new value gets added on May 4th, for example of $9250, the % change should be 11.11% (the % change of 9250 on May 5th vs 8324.99 on April 29th).
Hope I´ve made myself clear, thanks in advance! Regards.
We can use this formula to get the last numeric value in column B.
=INDEX(B2:B,MATCH(143^143,B2:B))
To get the 5th last value in column B, i.e. the total from 5 days ago we can adjust the same formula.
=INDEX(B2:B,MATCH(143^143,B2:B)-4)
We can then combine these formulas to get the percent change.
=(INDEX(B2:B,MATCH(143^143,B2:B))-INDEX(B2:B,MATCH(143^143,B2:B)-4))/INDEX(B2:B,MATCH(143^143,B2:B)-4)
Of course, format the cell with that formula to Percent. (Format>Number>Percent)