Feb 28 2022 08:21 AM
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.
Feb 28 2022 11:33 AM
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)
Feb 28 2022 12:45 PM
@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.
Feb 28 2022 01:47 PM
Feb 28 2022 02:51 PM
Mar 01 2022 07:26 AM
Mar 01 2022 08:04 AM
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.
Mar 01 2022 09:18 AM
@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:
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)
Mar 02 2022 07:32 AM
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.
Mar 02 2022 09:12 AM
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)
Mar 02 2022 05:56 PM
Mar 02 2022 07:20 PM
Mar 03 2022 06:38 AM - edited Mar 03 2022 07:00 AM
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.
Mar 03 2022 07:05 AM
Mar 03 2022 07:10 AM
Mar 03 2022 07:17 AM
Mar 03 2022 07:48 AM - edited Mar 03 2022 08:08 AM
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.
Mar 03 2022 08:40 AM - edited Mar 03 2022 08:41 AM
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.
Mar 03 2022 09:36 AM
@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).
Mar 03 2022 09:47 AM
Mar 03 2022 04:42 PM
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.