Forum Discussion
Formula keeps incrementing when inserting rows
First, FILTER() is available only o Excel 365. I guess it works for you, but if you share workbook with someone else the person also shall have 365.
With D:D and A:A we select entire columns D and A. Alternatively you may use something like D2:D1000 and A2:A1000, the only it's important that the ranges have the same size. Even better to use dynamic ranges but that's another step, not critical with this model.
FILTER(range, condition1*condition2*condition3) returns the range filtered by conditions. Multiplication here is equivalent of AND() operation. FILTER doesn't work with AND() and OR(), if you need the latest use sum of conditions with +.
First condition returns the records for which date is less than A3, second one records with dates greater than 7 days behind A3. Third condition filters empty cells. Finally we average returned from column D filtered range.
First, FILTER() is available only o Excel 365. I guess it works for you, but if you share workbook with someone else the person also shall have 365.
With D:D and A:A we select entire columns D and A. Alternatively you may use something like D2:D1000 and A2:A1000, the only it's important that the ranges have the same size. Even better to use dynamic ranges but that's another step, not critical with this model.
FILTER(range, condition1*condition2*condition3) returns the range filtered by conditions. Multiplication here is equivalent of AND() operation. FILTER doesn't work with AND() and OR(), if you need the latest use sum of conditions with +.
First condition returns the records for which date is less than A3, second one records with dates greater than 7 days behind A3. Third condition filters empty cells. Finally we average returned from column D filtered range.
