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

Greedy

New Member
Joined
Apr 22, 2006
Messages
48
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!)
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Greedy

New Member
Joined
Apr 22, 2006
Messages
48
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Apr 22, 2006
Messages
48
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
ABCDE
19
2-0.594833
34
435
527
668
777
889
960
1036
11212
1246
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))
 

Forum statistics

Threads
1,141,678
Messages
5,707,782
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top