 View Question
Q: Microsoft Excel Functions and changing cell addresses ( Answered ,   0 Comments ) Question
 Subject: Microsoft Excel Functions and changing cell addresses Category: Computers Asked by: bob64105-ga List Price: \$25.00 Posted: 10 Oct 2004 15:52 PDT Expires: 09 Nov 2004 14:52 PST Question ID: 412923
 ```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.``` Subject: Re: Microsoft Excel Functions and changing cell addresses Answered By: joey-ga on 10 Oct 2004 20:04 PDT Rated: ```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```
 bob64105-ga rated this answer: `Thank you very much!`  