Apllying a formula to a subset of cells in a range only

[Greedy]

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!)

 

[Aladin Akyurek]

What is the full SLOPE formula and is the first value in B2?

 

[Greedy]

Hello,

The full formula is

for col e data: =SLOPE(E500:E550,A500:A550)
for col f data: =SLOPE(F500:F550,A500:A550)

and first value begins in row 4 for all columns

G

 

[Aladin Akyurek]

Hello,

The full formula is

for col e data: =SLOPE(E500:E550,A500:A550)
for col f data: =SLOPE(F500:F550,A500:A550)

and first value begins in row 4 for all columns

G

A1:

=MATCH(9.99999999999999E+307,A:A)-ROW(A4)+1

Then invoke:

=SLOPE(OFFSET(E4,$A$1-1,0,-50),OFFSET($A$4,$A$1-1,0,-50))

and copy across.

 

[Greedy]

Thanks for this but could you please explain what the formula is trying to doing? I understand the objective of second part but the first bit does not make sense to me. Why are you trying to match 9.9999+307? What's the point of this?

The value returned by this formula is zero.

Thanks,

G

 

[Aladin Akyurek]

Thanks for this but could you please explain what the formula is trying to doing? I understand the objective of second part but the first bit does not make sense to me. Why are you trying to match 9.9999+307? What's the point of this?

Determine the position of the last numeric record in column A.

The value returned by this formula is zero.
...

Really? What do you have in column A when you get 0?

Here is an exhibit for the last 5 values...

aaRangeOfLastFiftyCells Greedy.xls
	A	B	C	D	E
1	9
2	-0.59483				3
3					4
4	3				5
5	2				7
6	6				8
7	7				7
8	8				9
9	6				0
10	3				6
11	2				12
12	4				6
13
Sheet1

A1:

=MATCH(9.99999999999999E+307,A:A)-ROW(A4)+1

B1:

=SLOPE(OFFSET(E4,$A$1-1,0,-5),OFFSET($A$4,$A$1-1,0,-5))