Hello,
This should be pretty simple to do but I'm not sure what the best way is and would really appreacite some help. I have a cells of data in column be which is updated a few days with the newest data appearing at the bottom.
I have a formula to calculate the slope of the last 50 values in the column (=slope(b500:b550). But everytime I update the column with the lastest data I need to adjust the formula to capture only the last 50 cells. So after adding 10 new values, my formula needs to be changed from =slope(b500:b550) to =slope(b510:b160).
Is there a way to avoid having to change the formula manually everytime the series grows? I was thinking of some function that finds the last populated cell in a range (say b500:b2000) and counts backwards by 50 to get the range which then feeds into the formula. I was thinking about using some kind of dynamic range but I also have data in other columns (30) that I need to calculate eqv slopes for so don't want anything that would be too complex/time consuming to replicate for the other columns.
Thanks,
Greedy (for some help!)
This should be pretty simple to do but I'm not sure what the best way is and would really appreacite some help. I have a cells of data in column be which is updated a few days with the newest data appearing at the bottom.
I have a formula to calculate the slope of the last 50 values in the column (=slope(b500:b550). But everytime I update the column with the lastest data I need to adjust the formula to capture only the last 50 cells. So after adding 10 new values, my formula needs to be changed from =slope(b500:b550) to =slope(b510:b160).
Is there a way to avoid having to change the formula manually everytime the series grows? I was thinking of some function that finds the last populated cell in a range (say b500:b2000) and counts backwards by 50 to get the range which then feeds into the formula. I was thinking about using some kind of dynamic range but I also have data in other columns (30) that I need to calculate eqv slopes for so don't want anything that would be too complex/time consuming to replicate for the other columns.
Thanks,
Greedy (for some help!)