Feb 28 2022 08:21 AM
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.
Mar 03 2022 09:55 AM
Mar 03 2022 10:11 AM
Mar 03 2022 11:09 AM
@wcstarks The cells in the upper right were just to demo what could be done using that Name Function
I updated the graph Legend to show both series
I realized the formula for the 7-day average was reverted back. I inserted the formula I created.
Hope it works.
Mar 03 2022 02:08 PM
Mar 03 2022 02:47 PM
Actually, In my current version, I looked in Name Manager and in the graph and your updates seem to be there. Attached, is the one I am using now. Your test columns V and W are working ok. I am wondering now, just what I sent you earlier. I thought it was this one.
Mar 03 2022 03:31 PM - edited Mar 03 2022 04:03 PM
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.
Mar 03 2022 04:42 PM
Solution@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.
Mar 04 2022 07:00 AM