Formula keeps incrementing when inserting rows

[wcstarks]

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

[#2]

Oops, I meant to say 7 day running average of the Glucose readings.

[#3]

@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.  

[#4]

1. The data start in row 3, correct?  Row 2 is empty except for the formula.
2. 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))

@wcstarks 

[#5]

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.

[#6]

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.

[#7]

@wcstarks 

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<>""))
)

[#8]

=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?

[#9]

@wcstarks 

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.

[#10]

=AVERAGE(FILTER(D:D,(A:A<=$A$3)*(A:A>=($A$3-7))*(D:D<>""))
This 3rd option seems simpler to deal with, but I don't understand all the details. Do you have time to explain the use of each element? for Example, I don't know what D:D or A:A does? What does the "-7" do? And what does the multiplication do?

[#11]

Sorry. Ignore my question about the -7.

[#12 · Accepted solution]

@wcstarks 

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.

[#13]

I made response, but when I attempted to post it, it somehow, it got lost, so I'll do it again. When I copy and pasted your filtered average formula into row J2, Excel complained about the syntax and added a missing closing ")" on the end, an easy thing to miss. When I ran it as written, all rows received the same value as cell J2. As with the OFFSET AVERAGE formula, I tried removing the "$"s in your formula, and found that it now works correctly--all rows have their unique values again. Perhaps you can explain why you thought you needed to use the $s and why they did not work.

Your 3rd filter condition also fixed another problem I had not reported. When a glucose cell is missing a value (missed doing a glucose reading before the meal), the calculations ceased from that point on. No new rows would get calculated, forcing me to enter a estimated "dummy" value to keep the calculations working. Now, with the filter condition, a glucose reading can be missing and the calculations continue past the affected row.

I tested inserting two rows above row 3 for today, and found that the function continued to work properly. It started it calculations with row 5, which had been row 3, the last row with no empty values. It ignored all rows with no glucose values. Thanks for your help.

[#14]

Using the filter function causes Excel to slow down when adding new rows or when changing values in the glucose column. It takes nearly 3 seconds to update.

[#15]

@Sergei Baklan 

Actually, the filter function does not seem to be working as expected.  Look at the actual Excel document for sheet Q1.  After getting strange results with the inserted rows and the dummy row 2, I removed the dummy row 2 and the two inserted rows.  Now, J2 for each row for the same day gets the same value.  I would expect that each row in the same day would get its own unique value, but that is not the case, now.  You can play with it and see what is happening.  Insert a row and see what happens.  Then give it a date and see the result in the inserted row. Then add a glucose i.e. 150, and see what happens.  I may need to return to the Average-Offset function.

Actually, when inserting a new row, or changing a date or the glucose values, it take over 3 seconds for Excel to update when using the filter function.

[#16]

@wcstarks 

Since you use structured table let change formula on

=IFERROR(AVERAGE(
  FILTER([Glucose],
     ([Date]<=[@Date])*
     ([Date]>=([@Date]-7))*
     ([Glucose]<>""))
),"")

It closes the performance issue, please check attached.

Same average for same date - yes, that's in accordance to initial logic. We keep average for 7 days, let assume we use zero instead of 7. Then it'll be average for the same day, it doesn't matter how many records we have for this day, average will be the same for each of them.

That could be another logic, e.g. from Time Slot value at this day till same Time Slot value 7 days ago. But you have long list of Time Slot values, it's not clear which logic to use if 7 days ago there was no same Time Slot.

Or you may use average for latest 14 records as it was initially suggested, doesn't matter how many days it'll be. Even in this case I'd don't use OFFSET, better something like

=AVERAGE([@Glucose]:INDEX([Glucose], ROW()-ROW(tblQ1[[#Headers],[Glucose]])+14))

Above is not working formula, only an idea if go in this direction - adjustment is required for the last 14 records in the table.

[#17]

@Sergei Baklan 

Thanks for your help.  You may recall, that originally, I kept row 2 as a dummy row and inserted new rows from row 3.  Then, as we worked through the formulas, I ended up getting rid of the dummy row 2.  I forgot that one reason I had the dummy row, was because it caused a problem with the Carbs/Day calculation when I inserted new rows from row 2.  Attached is the file you sent me modified with the two rows inserted from row 2.  It includes the date and both sample glucose readings and sample carbs values. You will notice that all the previous days, when the second entry for the day is entered, both the morning entry and the evening entry show the total carbs for the day, similar to how your formula shows the same value for the average glucose values for the day.  Well, as you can see, when I insert new rows from row 2, the carbs per day no longer show the total carbs/day in both rows.  The first row shows its carbs and the second entry shows the sum of the values from both entries for day.  The only way I can fix the issue is to insert below the last good row and copy the data from the last good row then delete the last good row.  Then insert from that new row 3 to make a new dummy row 2 and then insert new rows from the new row 3. Then, the formula will again do the total carbs/day in both the morning and evening rows.  As you can see, I have a graph that shows carbs for each row and the total carbs/day.  The chart looks much better when both rows for the day show the same value.  Do you understand why the formula does not work the same when new rows are inserted from row two than when they are inserted from row 3?  See attached.

[#18]

I suspect we've discussed this as much as any of us want. :)  So, this will be my last post in this discussion.  I also assume the version of Excel in use supports the necessary formulas and features.

The idea behind the below is that I've never been a fan of asking someone to "make space at the top" to enter data, though, sad to say, I've done it.  But with the newer capabilities of Excel, we can avoid that kind of UI/UX.

With the dates in column A and the readings in column D:

For a 7 day average for the most recent 7 days, use

=AVERAGEIFS(D:D,A:A,">="&MAX(A:A)-7)

To get a list of the latest 7 days and the average reading per day, I'd use the dynamic array formulas:

In N4:

=MAX(A:A)-SEQUENCE(7,,0)

In O4:

=AVERAGEIFS(D:D,A:A,N4#)

@wcstarks 

[#19]

@wcstarks 

Not sure I understood. If we speak about Carbs / Day formula it could be like

=IF(
  ([@[Time Slot]]="")*
  (ROW()-ROW(tblQ1[[#Headers], [Time Slot]]))=2, [@Carbs],
  SUMPRODUCT([Carbs]*(INT([Date])=[@Date]))
)

see Carbbs2 column in attached. 

If Glucose 7 day avr shall we exclude these dummy rows from average?  But that affects only these dummy rows.