SOLVED

Average values for each day in a Table, plus two other issues

Iron Contributor

I have three somewhat related issues.

1) Earlier, Hans Vogelaar helped me modify my original formula to =SUMIF([Date],[@Date],[Carbs]) to calculate the total carbs for each day to simplify referencing. I would now like to calculate the average [Glucose] for each day in the table in column "P". I looked up the AverageIf() function and attempted to emulate the SumIf() function to create the AverageIf(), by replacing SumIf with AverageIf, and [Carbs] to [Glucose], as:

=AVERAGEIF([Date],[@Date],[Glucose])

But this does not work.

 

2) I have a graph lower down on the table which displays the "Glucose" and the Glucose approximate "7 Day Average" for the row range of 3-50. I insert at row 3 to add each new entry to keep the most current record at the top. However, each time I add a new row, it increases the each row range value by 1, changing the row range to 4-51, 5-52, etc.. I need to keep the range at fixed values.

Formula: ='Q 1'!$F$3:$F$50,'Q 1'!$M$3:$M$50

This formula can only approximate the 7 Day average, as the number of records per day varies. I presume there is a way to get it to do exactly 7 days.

 

3) I keep row 2, next to the header hidden, because if I use it, it modifies this graph formula, making it longer, and the graph stops updating.

 

Any help I can get would be greatly appreciated. See attached file.

 

27 Replies

@wcstarks 

1) The title of the Glucose column is actually " Glucose" with a space at the beginning, so the formula has to be

=AVERAGEIF([Date],[@Date],[[ Glucose]])

(Alternatively, remove the space from before the word Glucose in cell F1)

 

2) and 3) This makes no sense to me - why use only rows 3 to 50?

To calculate a 7-day average, use AVERAGEIFS:

=AVERAGEIFS([[ Glucose]],[Date],">="&[@Date],[Date],"<"&[@Date]+7)

@wcstarks  so i attached an updated file and see that Hans already replied (I got pulled away on actual work).  So I used a slightly diff formula than Hans:

=AVERAGE([@[ Glucose]]:INDEX([ [ Glucose] ],MATCH([@Date]-7,[Date],-1)))

In my case it averaged THIS down through the last 7 days while his formula averages the last 7 days.  The difference being that values in my case may be different in a single day while his is truly based on days.

That aside i also created NAMES for Glucose 7 day and Glucose 7 day average and then used those names in the graph so it will always be looking at the same range.

That all said, you might consider doing something else because right now if you take 50 measurements 1 day and only 2 another day the graph just shows 52 measurements with no indication that 50 of them are 1 day and only 2 are another day.

1) Yes. After I got Hans' response, I realized I had asked for the wrong solution for the 7 Day average. You provided the solution that I should have asked for in the first place. I want each row to have its own average back through the seven days, like you did it. In the mean time, I removed the leading space in "Glucose".
2) I don't see a change in the graph functions. They still reflect my original syntax which has issues.
I only changed the Glucose graph on the 'Q 1' tab as that is the tab we were working on and the other graph on that tab is for Carbs and looking at data from a completely different tab so i wasn't sure what was going on there.
As for the Glucose graph, the change is that it calls a NAME function for the data sources and those Names dynamically find the most recent 7-days. If you insert a new row 3 and give it the date 2022-03-05 you will see the chart significantly change and have much less data since it will now look at the date range 2022-02-26 to 2022-03-05 (the new latest date).
BTW, in addition to the space in front of Glucose you also had 2 bad dates (there could be more that I didn't find). 1 of them was the year 2222 and the other was the month 09 instead of 01. I fixed them on the sheet i sent but wanted to let you know in case you go back to the other sheet for some reason.
I might recommend a conditional formatting that will highlight a date that is > or < 10 days (or pick your value) from the previous date.
Thank you.
1) I hadn't noticed the leading space in the column name and I couldn't figure out why the reference didn't work. I have fixed it. Now the formula in column "P" works just fine.

2) In the graph formula, 50 is an arbitrary number which defines the horizontal resolution of the graph. As that number grows, the graph gets more and more crowded. Number 3 in the graph formula keeps the graph from looking at the dummy row 2, which always contains a zero. As I mentioned before, each time I insert a new row above 3 to enter a new record, both those numbers in the formula increment by one, which is a problem. I need for these values to remain constant as I add new rows to the top of the table. It used to be that only the 50 got incremented, but now, since these recent edits, it also increments causing the graph to move backward in time. It is interesting that the Carbs graph dose not have these issues.
Glucose Graph Formula as currently written: ='Q 1'!$F$3:$F$50,'Q 1'!$M$3:$M$50, before the numbers get incremented.

3) I currently need to keep row 2 empty, because if I fill it in, the graph formula somehow gets modified, making it longer and it stops updating. Because of this, I have hidden row 2 to prevent me from accidentally entering data into it and messing up the graph formula. I have no clue why adding data to row 2 causes this behavior. Perhaps it has something to do with row 2 being next to the header row.

4) Considering your 7 Day Average formula, I had requested the wrong solution. I need it to let each row reflect the average up to that point, not for the average to be the same for the whole day. mtarler provided the solution I was really looking for. See his response.

@mtarler 

Thank you for your response.
1) Your 7 Day Average formula works great.
2) I understand you to say that you modified the Glucose graph formula to use named references, but this is the formula for the Glucose graph as downloaded from your post: "='Q 1'!$F$3:$F$50,'Q 1'!$M$3:$M$50," after the bottom range was reset to 50 from 54. It appears to be unchanged. So, I am not sure what you are talking about in your second paragraph. Please read my response to Vogelaar (particularly paragraph two. It sounds like what you are describing would be nice, but I don't see it in your download version.

3) I have updated the file I downloaded from you, which still contains the old graph formula. Now, when I insert a new row above row 3, it puts in the old formula in column P into column P instead of bringing the formula forward from row 3.  I have had this happen before, but I don't know how to resolve it.

@wcstarks I don't know if we are talking the same thing.  Also the file you attached has a lot of references to another copy of the document, which can cause problems.  Here is a pict of what I'm talking about with the graph:

mtarler_0-1646154859794.png

Notice how that data line is ref to " 'Linda Tracking (ADR)-New2.xlsx'!Gluc7day"

Which is a NAME in the workbook NAME manager that I created to dynamically find the data of the most resent 7 days.  In the orig sheet it starts are row 3 and includes data >= MAX date - 7  (in the attached I deleted that problem row 2 and start in row 2)

@mtarler 

I downloaded and opened your latest version of the file.

1) I added dummy data into the empty row two, changing the date to 3/1/22. I found that the graph formula changed from showing rows 2-21, to showing rows 2-49. Then I inserted a new row 2 and found that the graph changed from showing rows 2-49, to showing rows 2-50. I presume that is because it now took down to the 50th row to show all rows for that date?

 

2) I then entered data into the newly inserted row 2, and as I had mentioned before, found that the [Daily Ave] column formula was replicated out of thin air, as it were, with the old formula, rather than from the formula in the now row 3. This produces an error. Why is this so, and how can we get it to replicate properly?

 

3) I then looked in the name manager to see the two new names you created. At my skill level, I do not understand how these names are able to modify the graph formula, since the graph formula does not seem to reference them. Would you please explain how that works?

 

4) You say that this file has references to other files. How do you know that, and if that is a potential issue, how do I fix that?

 

I have attached the newly downloaded file with my modifications.

@wcstarks 

1) so it went from 2-21 to 2-49 because I had entered a date like 03/05/2022 so the most recent 7 days  only included 02/26 and later which was rows 2-21.  When you changed that value to 3/1 the most recent 7 days goes back to 2/22 and down to row 49.  When you added another 3/1 entry the most recent 7 days was still 2/22 but that day was now bumped down to row 50

2) I don't know why except to guess that the new formula was originally added to row 3 (row 2 was hidden) and then filled down and excel still considered the 'default' formula for the column to be the old even after row 2 was deleted.  I deleted the entire column and re-pasted the new formula in and i think it is behaving now

3) the formula in the Name Manager doesn't modify the graph.  The formula defines a RANGE using those functions that will include the corresponding column from row 2 to 7 days earlier.  That NAME could be used for anything per se.  For example you could add a formula to cell W1 =AVERAGE(Gluc7day) and that will show the average value over the most recent 7 days.  So that said, I then replaced the normal range references for SERIES 1 and SERIES 2 in the graph with those NAMES so the graph will use those dynamically calculated ranges.

4)  the file HAD external references.  If you go back to the file you posted earlier you could find them in some of the formulas but I deleted any that I found (and don't see any in the one I just opened)

Thank you. I downloaded the latest and updated it with my new data during the interim. So things seem to working well. I understood that you replaced the original range formula with dynamic range names. I did look at the Glucose graph "cart data range" formula again, but found it was still the original formula. So, I was initially confused. After that, I "edited" series for the chart range and discovered that you had put your named variable in there.

After adding several rows from my current data, I re-checked the formula again and found the max range had changed from 49 to 53. I checked row 53 and found it is still for the same day as row 49. To test further, I added 3 more rows for 3/2/22 and found the range increased to 56, which is the first entry for that same day (23rd). I then changed the day of the week for those 3 new rows to 3/3/22 and found the range returned to 49, which represents the first day of the next day (24th). So, it appears to be working as is. I see now why I was so confused. I had long forgotten that the data range formula was actually defined within the two series. I was only looking at the chart data range. Incidentally, I renamed each series to reflect their function. They used to be named properly, but those names had reverted back to series x.

Thanks so much for all your help. When I add a new row, column P shows an error until I enter a date. I'll see if I can include an if statement in the formula to avoid that.
glad it is all working. if that error bothers you, you can add an IFERROR( ... ,"") around it, but since I expect you insert row and immediately add date and data I personally wouldn't bother. You don't want an empty row there because the graph will also take it as a 0.

Actually, I found that the new row has begun to open extra high, I had presumed because of the long error text in column P. So, I ended up adding the iferror() function as suggested. While that works, Excel has developed the behavior of opening up the new row extra high, each time I insert a new row, forcing me to manually select auto fit each time. Experimenting, I found it only has this behavior when inserting on row 2. If I insert at other rows, it behaves normally. Any suggestions? The height seems to be about the same size as the header row. See attachment and insert at row 2.

yes, new insert rows take on properties like the row above it.
could create a 'dummy' row 2 and change all the formulas to start on row 3 ...
another option is to highlight row 2, hit copy, hit insert copied cells, update values
Thanks. So, I may be back to using a dummy row 2. I enter most of the data from the android phone app. I'll have to see how it works on the phone.
The other option is to use the table 'as intended' and instead of inserting new rows at the top, just type on the next row at the bottom (again formulas would need updating).
I do wish excel had a "entry row" option that was pinned at the top so you didn't need to scroll to bottom.

Yes. I am inserting at the top so that all entries are from current to older. I need the current day at the top. Finding the bottom and adding there is cumbersome and would require auto-sorting Z-A with the date and time. I sometimes do not have the current time, and may not enter it. With auto-sorting, that would mess up the sort order. All things considered, inserting at the top seems to really be the simplest option. Excel taking the formatting from the row above when inserting from below is also a dumb feature in Excel.

@mtarler 

Now, when I insert new rows the starting row increment in the graph causing the graph to ignore the more current entries. What did I miss? See attachment. Your function in column W is also not working.

@wcstarks Did you go back to an old sheet?  The graphs moved back down and have the old series formulas.  I updated the Name formulas to use row 3 ...   and updated the graph to use the Name formulas. (I didn't move them this time in case you really want them there).

I didn't think so. I did have a syncing issue between my phone and my computer. Windows created a copy which I went back to, but I would think it would have been the current version. But perhaps not. Sorry for the trouble.
1 best response

Accepted Solutions
best response confirmed by wcstarks (Iron Contributor)
Solution

@wcstarks So the formula in col M should read:  =AVERAGE([@Glucose]:INDEX([Glucose],MATCH([@Date]-7,[Date],-1)))

as for what it is doing it is taking the AVERAGE over a range that starts at the Glucose value in THIS ROW (@) and goes to (:) a location based on the INDEX function.  The INDEX is looking in the entire Glucose column and returning the Nth location (based on the result of the MATCH function).  The MATCH is looking for the Day that is -7 from the Date in THIS ROW (@) and it is looking in the entire Date column.

The final -1 inside that Match just says the dates are sorted from biggest to smallest.

 

View solution in original post