Excel 2013.
How do I create an in-formula conditional criteria for an IFS-type function's criteria range based on subtracting a value from the value of the cell at the foot of the Criteria_range? What I'm trying to to is select/count/sum/average rows from the last seven days only from within criteria range which is always greater that seven rows, but less than some large number. Each row has a date, and some unique activity data in corresponding columns; some days have multiple activities (multiple rows), and some don't. Newer data is added to the bottom of the table. The data is sorted by date in ascending order.
I've tried, for example:
Assume the bottom of the Criteria_range1 (column D) is today's date, preceded by lower values (previous days), with some repeated date rows. The Average_range I want is in column E. I want it to pick the date value from D25, subtract 7, then use that as the greater-than criteria for averaging (or summing etc) rows that correspond to a date value of "within the previous 7 days" of the date in D25. But,
=AVERAGEIFS(E1:E25,D1:D25,">(D25-7)") returns only the #DIV/0! error code
=AVERAGEIFS(E1:E25,D1:D25,">44477") returns a valid result if I use an integer date value instead of a nested formula to create the date value I want.
I want a nested formula to compute the Criteria so I can pull down this formula for every row, and extend the moving average or sum-over-time to the past 30 or even 365 days
