Forum Home
Press F1
Thread ID: 37101	2003-08-28 11:12:00	Summing or averaging last n cells in Excel	ianbon (2532)	Press F1

Post ID	Timestamp	Content	User
171213	2003-08-28 11:12:00	Hi, I have a column in Excel where each day the temperature is entered in a cell, one day below the other. How can I, on any day I feel like it, get the sum (or average) of the last ten days?	ianbon (2532)
171214	2003-08-28 11:23:00	Highlight the readings for the last 10 days. The sum will show at the bottom of the screen. I presume you can get the average from there.	wotz (335)
171215	2003-08-28 11:44:00	If you Autofilter your data the function =SUBTOTAL can be used to give a value for only the filtered data. If you filter a list then attempt to Sum it Subtotal is automatically envoked. Use the Help facility for more details.	Russell D (18)
171216	2003-08-31 10:44:00	thanks people, but it looks like I didn't ask my question clearly enough. I want something automatic. I want a formula that will find the last filled-in (non blank) cell in a column and then add up the 10 entries above that.	ianbon (2532)
171217	2003-08-31 12:58:00	The following formula will work for Column A, provided it has entries in all cells from A1 to the end of the data - do a Find and Replace-All on the formula to alter the Column from A:A to your own column of data. =INDEX(A:A,COUNTA(A:A)-1)+INDEX(A:A,COUNTA(A:A)-2)+INDEX(A:A,COUNTA(A:A)-3)+INDEX(A:A,COUNTA(A:A)-4)+INDEX(A:A,COUNTA(A:A)-5)+INDEX(A:A,COUNTA(A:A)-6)+INDEX(A:A,COUNTA(A:A)-7)+INDEX(A:A,COUNTA(A:A)-8)+INDEX(A:A,COUNTA(A:A)-9)+INDEX(A:A,COUNTA(A:A)-10) There is probably a simpler way by using an array formula - but this works for now. HTH	Russell D (18)
171218	2003-08-31 13:41:00	>There is probably a simpler way by using an array formula .. this is it - it's not an array formula tho' - same rules apply re no blank cells =SUM(OFFSET(A1, COUNTA(A:A)-11,0,10,1))	Russell D (18)
171219	2003-09-01 02:12:00	thanks a lot Russell. that's great. Ian	ianbon (2532)
1