Forum Discussion
Formula keeps incrementing when inserting rows
I am keeping a 7 day rolling average of daily carbohydrate intakes. The formula for the column is defined in row 2 and automatically replicated incrementally for each row. To keep the most current day's rows at the top, I insert each new row from row 3, leaving row 2 empty except for the formulas. The formula in row 2 is supposed to remain at =AVERAGE(D2:D16) and the second value should remain the same, so that rows 3 to n increase incrementally by row. Even though I do not modify row 2, as I add rows in position 3, the 2nd value in row 2 will increment from 16 to 17 to 18, ect. as each new row is added below it. I don't know how to force Excel to keep the value in row 2 constantly at D16. Everyday, I need to reset the value in the formula in row2 back to D16 and then update the whole column.
See attached image
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.
18 Replies
- 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))- Thanks for responding and suggesting to use the Offset function. That is what I needed to know.
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<>"")) )
wcstarks Try it this way:
=AVERAGE($D$2:OFFSET($D$2,14,0))D2 is the "fixed" starting point and the end of the range is determined by offsetting 14 rows down from D2.
- Thanks for the suggestion to use the offset function, which I was not familiar with. Actually, using the dollar signs did not work. Each row got the same value as the current row value. So, I tried removing the dollar signs, and now each row gets its unique value. To test, I inserted two rows for today's readings, and found that the formula remains consistent. Thanks.
- Oops, I meant to say 7 day running average of the Glucose readings.
