formula to scan a list and get last 5 values? please help

[rca]

Hello!

I have a dynamic named range (called "myRange") that is currently in cells C3:C45. Each day, this range 'grows' by 1 row. So, tomorrow, for instance, the range will be from C3:C46. I am trying to write a formula that would SUM up the last 5 values in this range. So, simplistically, the formula would just be =SUM(C41:C45). BUT, how do I write a SUM formula that also grows with the range. So, a few weeks from now, the formula would maybe read =SUM(C92:C96).

Is there a way to make the formula 'move' automatically as the range grows with time?

Thanks a lot!
Rachel

 

[rca]

Thank you both!

Now, here's a question... how do we write a custom function that allows the user (me ) to enter the following arguments:

1. Array
2. Last number of observations

So, the user would pass the array and the last n number of observations that she/he wanted the SUM of.

How would you do that? Is it very tricky?

Thank you,
Rachel