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.
The formula in Column M in your most recent update looked like this: "=AVERAGE([@Glucose]:INDEX(#REF!,MATCH([@Date]-7,[Date],-1)))"
It doesn't work and I don't know how to fix it.
I tried using your formula in column W for Column M, but it makes all the entries the same value.
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.
- Thanks so much for the explanation. I was able to adapt the same function to use in the A1C column. It needs the last 90 days and the result is divided by a certain factor. All seems to be working well.