Average values for each day in a Table, plus two other issues

[wcstarks]

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.

[#2]

Is the data in columns V and W part of the design, or are they just for tests?

[#3]

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.

[#4]

@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.

[#5]

Excel complains of corruption in this file. Would you please verify and re-attach the file?

[#6]

@mtarler 

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.

[#7]

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.

[#8 · Accepted 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.

[#9]

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.