automtically change ranges in formulas when new data is entered

[Guest]

Using Excel 2003. New data is added to an exsisting worksheet on a daily
basis. I want my formulas to always apply to the last five cells of data
entered without having to manually update the ranges. For example: Today,
my range is A1:A5. Tomorrow, the range for the formula will be A2:A6. Is
there a way to automatically update the ranges after a new cell is entered?
Thank you!

 

[Guest]

Try this:
For values in Cell A1 through whatever

B1: =SUM(OFFSET(A1,COUNTA($A:$A)-5,0,5))
Returns the sum of the last 5 items in the list.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Aladin Akyurek]

Moreover, since you are on Excel 2003, convert the data area into a list
by means of Data|List|Create List to enjoy fully automatic adjustment of
formulas for references.

 

[Guest]

having trouble applying this formula to a row instead of a column

 

[Guest]

If the numbers are in row 1, beginning in A1 and extending to the right...

Try this:
B1: =SUM(OFFSET(A1,0,COUNTA($1:$1)-5,1,5))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

Thanks Ron! I had tried (A1, 0, COUNTA (1:1)-5, 0, 5) and (A1, 1, COUNTA
(1:1)-5, 1, 5). This was the first time I ever had to use this formula and I
appreciate your help!