I want to keep lists of daily values in MX Excel 2003 and perform
functions like average and sum on the last x values in the list
without having to redo the function.  Suppose I'm on a diet, and each
day I add my weight that morning to the bottom of Column A of my
sheet.  In cell C1, I want to show the average of the last seven
entries without manually updating the formula, even though my list is
getting longer every day.  Or I might be tracking the number of pages
of homework I read each day, and want to have a running total of the
last week.

I do want my total/average to appear on the same place on the sheet
each day so that I can link to the summary area in a Word document.

Hi there.

One solution is to do the following:

Assuming Column A has your list of numbers, enter in G1 the number of
spaces from the bottom you'd like to be counting/summing/averaging.

Then, in C1, enter the following:

=AVERAGE(OFFSET(A:A,COUNT(A:A)-G1,0,G1,1))

-or-

=SUM(OFFSET(A:A,COUNT(A:A)-G1,0,G1,1))

etc.

What this does is look at a range based on the height of your current
one, made smaller by the amount listed in G1, and then "offset" --
e.g. moved -- based on the length of the full range less what's in G1.

If you'd rather hardcode the number of spaces from the bottom to be
used in the summing, etc., just replace G1 in both spots in the chosen
formula with the number of spots.

Please let me know if you have any questions regarding this.

--Joey


Searching strategy: excel offset function

Thank you very much!