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.
I do wish excel had a "entry row" option that was pinned at the top so you didn't need to scroll to bottom.
- 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.
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.
- Excel complains of corruption in this file. Would you please verify and re-attach the file?
- When I print, I usually only print the 1st page--the most recent entries. The graphs then print at the bottom of the page. Oddly, the graph only shows the "Glucose" legend label and not the "7 Day Average" legend. I've tried re-editing it, but it won't come back. It really isn't critical. It seems Excel is very finicky.
- Is the data in columns V and W part of the design, or are they just for tests?
- I didn't think so. I did have a syncing issue between my phone and my computer. Windows created a copy which I went back to, but I would think it would have been the current version. But perhaps not. Sorry for the trouble.
Yes. I am inserting at the top so that all entries are from current to older. I need the current day at the top. Finding the bottom and adding there is cumbersome and would require auto-sorting Z-A with the date and time. I sometimes do not have the current time, and may not enter it. With auto-sorting, that would mess up the sort order. All things considered, inserting at the top seems to really be the simplest option. Excel taking the formatting from the row above when inserting from below is also a dumb feature in Excel.