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.
- The data start in row 3, correct? Row 2 is empty except for the formula.
- Also, if you want a 7-day average, the formula should reference only 7 days of data, not go all the way to D16, which would give you a 14 (or 15) day average.
In any case, to get 7 days of data starting with D3 and not have to adjust the formula when you push data in D3 down each day, use
=AVERAGE(OFFSET(D2,1,0,7,1))
1) Yes. I leave it blank, primarily because it seems that if I insert on row 2, next to the header row, the formatting of the inserted rows gets messed up. I don't know why.
2) If you look at my attachment, you will see that my wife normally does two readings a day, so 14 rows approximates 7 days. If she were to occasionally skip a reading, or do an extra reading, then 14 rows would not truly reflect exactly 7 days, but for the purpose, such accuracy is not needed. I also have a corresponding line graph which shows each reading and the "7" day averages.
3) Before getting to your response, I had tried Riny's: =AVERAGE(D2:OFFSET(D2,14,0)) without the $s and it seems to work OK. Not being sure what the additional parameters are, which are reflected in your formula, I'll will look up the Offset function to find out.
As variant that could be for
with
=AVERAGEIFS( $D$3:$D$1001, $D$3:$D$1001, "<>", $A$3:$A$1001, "<=" & $A$3, $A$3:$A$1001, ">=" & $A$3-7 ) and similar within column for running average or =AVERAGEIF( $D$3:INDEX($D$3:$D$1000,MATCH($A$3-8,$A$3:$A$1000,0)-1),"<>0", $D$3:INDEX($D$3:$D$1000,MATCH($A$3-8,$A$3:$A$1000,0)-1)) or =AVERAGE( FILTER(D:D,(A:A<=$A$3)*(A:A>=($A$3-7))*(D:D<>"")) )- =AVERAGE(D2:OFFSET(D2,14,0)) that I got in my first response (but with $s removed) seems to work OK. Is there some advantage to these more complicated formulas?
Since we have Date column it'll be more correct to calculate average for 7 days based on it, not taking exactly 14 records. With running average down to column J that's could be up to couple of records difference. Plus OFFSET is volatile function.
Perhaps both are not critical for this model, however why not to indicate different possibilities.