Forum Discussion
Average values for each day in a Table, plus two other issues
I have three somewhat related issues.
1) Earlier, Hans Vogelaar helped me modify my original formula to =SUMIF([Date],[@Date],[Carbs]) to calculate the total carbs for each day to simplify referencing. I would now like to calculate the average [Glucose] for each day in the table in column "P". I looked up the AverageIf() function and attempted to emulate the SumIf() function to create the AverageIf(), by replacing SumIf with AverageIf, and [Carbs] to [Glucose], as:
=AVERAGEIF([Date],[@Date],[Glucose])
But this does not work.
2) I have a graph lower down on the table which displays the "Glucose" and the Glucose approximate "7 Day Average" for the row range of 3-50. I insert at row 3 to add each new entry to keep the most current record at the top. However, each time I add a new row, it increases the each row range value by 1, changing the row range to 4-51, 5-52, etc.. I need to keep the range at fixed values.
Formula: ='Q 1'!$F$3:$F$50,'Q 1'!$M$3:$M$50
This formula can only approximate the 7 Day average, as the number of records per day varies. I presume there is a way to get it to do exactly 7 days.
3) I keep row 2, next to the header hidden, because if I use it, it modifies this graph formula, making it longer, and the graph stops updating.
Any help I can get would be greatly appreciated. See attached file.
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.
27 Replies
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.
