Forum Discussion

wcstarks's avatar
wcstarks
Iron Contributor
Feb 28, 2022
Solved

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 referenci...
excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Mar 04, 2022

    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.

     

HansVogelaar's avatar
HansVogelaar
MVP
Feb 28, 2022

wcstarks 

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)

Resources