Forum Discussion
Average values for each day in a Table, plus two other issues
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.
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.
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)
- Thanks so much for the explanation. I was able to adapt the same function to use in the A1C column. It needs the last 90 days and the result is divided by a certain factor. All seems to be working well.
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.
The formula in Column M in your most recent update looked like this: "=AVERAGE([@Glucose]:INDEX(#REF!,MATCH([@Date]-7,[Date],-1)))"
It doesn't work and I don't know how to fix it.
I tried using your formula in column W for Column M, but it makes all the entries the same value.
- Excel complains of corruption in this file. Would you please verify and re-attach the file?
- When I print, I usually only print the 1st page--the most recent entries. The graphs then print at the bottom of the page. Oddly, the graph only shows the "Glucose" legend label and not the "7 Day Average" legend. I've tried re-editing it, but it won't come back. It really isn't critical. It seems Excel is very finicky.
- Is the data in columns V and W part of the design, or are they just for tests?
- 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.
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.
- 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. - 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.
- 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 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.
- 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.
- 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.