Microsoft Excel Functions and changing cell addresses
Asked by: bob64105-ga
List Price: $25.00
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.
Re: Microsoft Excel Functions and changing cell addresses
Answered By: joey-ga on 10 Oct 2004 20:04 PDT
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
rated this answer:
Thank you very much!
|There are no comments at this time.|
If you feel that you have found inappropriate content, please let us know by emailing us at email@example.com with the question ID listed above. Thank you.
|Search Google Answers for|