Excel AVERAGEIF with TODAY () function as criterion
I have values in column c that I want to average if they happened in the last seven days (corresponding dates in column a). I cannot get the averageif function to work correctly, as I need every single chunk of data to be evaluated separately if it hits within the last seven days. this looks like a function to be used all the time, but i googled for 45 minutes and can't find anything.
source to share
I don't have Excel 2007 to test, but I'll take a snapshot:
= AVERAGEIF($A$2:$A$100, ">=" & (TODAY()-6), $C$2:$C$100)
I am assuming that "last 7 days" means versus TODAY () and not versus some other data row?
Use NOW () instead of TODAY () and "-7" instead of "-6" if you want time sensitivity and not just look at today and everything before 6 days ago (i.e. 7 in total days).
If each row of data should have an average of the last 7 days compared to itself and not compared to today (i.e. you want a different moving average to run on each row), use something like:
= AVERAGEIF($A$2:$A$100, ">=" & ($A2-7), $C$2:$C$100)
in line 2 and paste down, $ A2 will set to use the current date / time breakpoint of the line. If the dates are already sorted, you can optimize the formula using $ A2 and $ C2 instead of $ A $ 100 and $ C $ 100 (pasting down will increase the range for each additional row).
source to share