May 22 2021 12:57 PM
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
May 22 2021 01:01 PM
May 22 2021 09:57 PM
@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.
May 22 2021 10:24 PM
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))
May 23 2021 09:12 AM
May 23 2021 09:20 AM
May 23 2021 10:49 AM
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<>""))
)
May 24 2021 11:26 AM
May 24 2021 01:11 PM
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.
May 24 2021 02:29 PM
May 24 2021 02:41 PM
May 25 2021 01:10 AM
SolutionFirst, 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.
May 25 2021 08:38 AM
May 25 2021 09:27 AM
May 25 2021 09:58 AM
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.
May 25 2021 12:50 PM
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.
May 26 2021 04:23 PM
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.
May 28 2021 05:35 PM
I suspect we've discussed this as much as any of us want.
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
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#)
May 29 2021 09:09 AM
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.