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. Number 3 in the graph formula keeps the graph from looking at the dummy row 2, which always contains a zero. As that number grows, the graph gets more and more crowded. Now the formula in column "P" works just fine.Ģ) In the graph formula, 50 is an arbitrary number which defines the horizontal resolution of the graph. I might recommend a conditional formatting that will highlight a date that is > or < 10 days (or pick your value) from the previous date.ġ) I hadn't noticed the leading space in the column name and I couldn't figure out why the reference didn't work. 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. 1 of them was the year 2222 and the other was the month 09 instead of 01. If you insert a new row 3 and give it the date you will see the chart significantly change and have much less data since it will now look at the date range to (the new latest date).īTW, in addition to the space in front of Glucose you also had 2 bad dates (there could be more that I didn't find). 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.Īs 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. I presume there is a way to get it to do exactly 7 days.ģ) 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.Īny help I can get would be greatly appreciated. This formula can only approximate the 7 Day average, as the number of records per day varies. 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 insert at row 3 to add each new entry to keep the most current record at the top. I looked up the AverageIf() function and attempted to emulate the SumIf() function to create the AverageIf(), by replacing SumIf with AverageIf, and to, this does not work.Ģ) 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 would now like to calculate the average for each day in the table in column "P". 1) Earlier, Hans Vogelaar helped me modify my original formula to to calculate the total carbs for each day to simplify referencing.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |