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