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.
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.
2) I don't see a change in the graph functions. They still reflect my original syntax which has issues.
- I only changed the Glucose graph on the 'Q 1' tab as that is the tab we were working on and the other graph on that tab is for Carbs and looking at data from a completely different tab so i wasn't sure what was going on there.
As for the Glucose graph, the change is that it calls a NAME function for the data sources and those Names dynamically find the most recent 7-days. If you insert a new row 3 and give it the date 2022-03-05 you will see the chart significantly change and have much less data since it will now look at the date range 2022-02-26 to 2022-03-05 (the new latest date).
BTW, in addition to the space in front of Glucose you also had 2 bad dates (there could be more that I didn't find). 1 of them was the year 2222 and the other was the month 09 instead of 01. I fixed them on the sheet i sent but wanted to let you know in case you go back to the other sheet for some reason.
I might recommend a conditional formatting that will highlight a date that is > or < 10 days (or pick your value) from the previous date.Thank you for your response.
1) Your 7 Day Average formula works great.
2) I understand you to say that you modified the Glucose graph formula to use named references, but this is the formula for the Glucose graph as downloaded from your post: "='Q 1'!$F$3:$F$50,'Q 1'!$M$3:$M$50," after the bottom range was reset to 50 from 54. It appears to be unchanged. So, I am not sure what you are talking about in your second paragraph. Please read my response to Vogelaar (particularly paragraph two. It sounds like what you are describing would be nice, but I don't see it in your download version.3) I have updated the file I downloaded from you, which still contains the old graph formula. Now, when I insert a new row above row 3, it puts in the old formula in column P into column P instead of bringing the formula forward from row 3. I have had this happen before, but I don't know how to resolve it.
wcstarks I don't know if we are talking the same thing. Also the file you attached has a lot of references to another copy of the document, which can cause problems. Here is a pict of what I'm talking about with the graph:
Notice how that data line is ref to " 'Linda Tracking (ADR)-New2.xlsx'!Gluc7day"
Which is a NAME in the workbook NAME manager that I created to dynamically find the data of the most resent 7 days. In the orig sheet it starts are row 3 and includes data >= MAX date - 7 (in the attached I deleted that problem row 2 and start in row 2)