Excel AVERAGEIF with TODAY() function as criteria

Question

I have values in column c that i want averaged if they occurred in the last seven days (corresponding dates in column a). i can't get the averageif function to work properly, as I need each individual piece of data to be evaluated separately if it falls within the last seven days. this seems like a function that would be used all the time, but i googled for 45 minutes and couldn't find anything.

Solution

I don't have Excel 2007 to test, but I'll give it a shot:

 = AVERAGEIF($A$2:$A$100, ">=" & (TODAY()-6), $C$2:$C$100)

I assume the "last 7 days" means compared to TODAY(), not compared to some other row of data?

Use NOW() instead of TODAY() and "-7" instead of "-6" if you want time sensitivity, not just looking at today and anything back up to 6 days ago (i.e., a total of 7 days).

If each row of data should have an average of the past 7 days compared to itself rather than compared to today (i.e., you want a different rolling average in each row), use something like:

 = AVERAGEIF($A$2:$A$100, ">=" & ($A2-7), $C$2:$C$100)

in row 2 and paste down, the $A2 will adjust to use the reference point of the current row's date/time. If the dates are sorted already, you can optimize the formula by using $A2 and $C2 instead of $A$100 and $C$100 (pasting down will increase the range for each additional row).

Comments

[Joel McBeth]

The second function does not work as it will average every value that is greater than 7 days from the current row's date. Which includes the past 7 days and every day in the dataset after it.

[Fear605]

Good catch, @jcmcbeth. Since AVERAGEIF doesn't have a "between" operator, I think a solution would require an array formula rather than AVERAGEIF.