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)
- Thank you.
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. 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.
- 1) Yes. After I got Hans' response, I realized I had asked for the wrong solution for the 7 Day average. You provided the solution that I should have asked for in the first place. I want each row to have its own average back through the seven days, like you did it. In the mean time, I removed the leading space in "Glucose".
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.
