Forum Discussion
Average values for each day in a Table, plus two other issues
wcstarks So the formula in col M should read: =AVERAGE([@Glucose]:INDEX([Glucose],MATCH([@Date]-7,[Date],-1)))
as for what it is doing it is taking the AVERAGE over a range that starts at the Glucose value in THIS ROW (@) and goes to (:) a location based on the INDEX function. The INDEX is looking in the entire Glucose column and returning the Nth location (based on the result of the MATCH function). The MATCH is looking for the Day that is -7 from the Date in THIS ROW (@) and it is looking in the entire Date column.
The final -1 inside that Match just says the dates are sorted from biggest to smallest.
1) The title of the Glucose column is actually " Glucose" with a space at the beginning, so the formula has to be
=AVERAGEIF([Date],[@Date],[[ Glucose]])
(Alternatively, remove the space from before the word Glucose in cell F1)
2) and 3) This makes no sense to me - why use only rows 3 to 50?
To calculate a 7-day average, use AVERAGEIFS:
=AVERAGEIFS([[ Glucose]],[Date],">="&[@Date],[Date],"<"&[@Date]+7)
1) I hadn't noticed the leading space in the column name and I couldn't figure out why the reference didn't work. I have fixed it. Now the formula in column "P" works just fine.
2) In the graph formula, 50 is an arbitrary number which defines the horizontal resolution of the graph. As that number grows, the graph gets more and more crowded. Number 3 in the graph formula keeps the graph from looking at the dummy row 2, which always contains a zero. As I mentioned before, each time I insert a new row above 3 to enter a new record, both those numbers in the formula increment by one, which is a problem. I need for these values to remain constant as I add new rows to the top of the table. It used to be that only the 50 got incremented, but now, since these recent edits, it also increments causing the graph to move backward in time. It is interesting that the Carbs graph dose not have these issues.
Glucose Graph Formula as currently written: ='Q 1'!$F$3:$F$50,'Q 1'!$M$3:$M$50, before the numbers get incremented.
3) I currently need to keep row 2 empty, because if I fill it in, the graph formula somehow gets modified, making it longer and it stops updating. Because of this, I have hidden row 2 to prevent me from accidentally entering data into it and messing up the graph formula. I have no clue why adding data to row 2 causes this behavior. Perhaps it has something to do with row 2 being next to the header row.
4) Considering your 7 Day Average formula, I had requested the wrong solution. I need it to let each row reflect the average up to that point, not for the average to be the same for the whole day. mtarler provided the solution I was really looking for. See his response.