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)
wcstarks so i attached an updated file and see that Hans already replied (I got pulled away on actual work). So I used a slightly diff formula than Hans:
=AVERAGE([@[ Glucose]]:INDEX([ [ Glucose] ],MATCH([@Date]-7,[Date],-1)))In my case it averaged THIS down through the last 7 days while his formula averages the last 7 days. The difference being that values in my case may be different in a single day while his is truly based on days.
That aside i also created NAMES for Glucose 7 day and Glucose 7 day average and then used those names in the graph so it will always be looking at the same range.
That all said, you might consider doing something else because right now if you take 50 measurements 1 day and only 2 another day the graph just shows 52 measurements with no indication that 50 of them are 1 day and only 2 are another day.