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.
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?
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.
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.
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 😧
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#)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.
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.
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.
- 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.
- 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.
