How to Tell Excel to Evaluate Last Three Months (NOT Last 90 Entries)
January 19, 2015 8:18 AM   Subscribe

I have an Excel spreadsheet I use to track glucose readings. There’s a value diabetics use called “A1C” which looks at the last three months of readings. It's based on a blood test, but can be estimated by averaging your readings for the last three months. I try to track every day, but I can’t reliably say “look at the last 90 values” – so I’m wondering how to set up a formula to tell it to only look at readings going back 90 days, not necessarily 90 readings. More inside.

The formula to convert a glucose reading to an A1C value is =(([value]+77.3)/35.6). So converting an individual reading isn’t a problem.

But, basically, I’d like to create a new column for my spreadsheet which gives me a sort of running A1C average – always looking back three months from the current entry and telling me what the average of the last three months of readings would be. But, as said above, I can’t tell it to merely look back 90 entries, since if I missed a few days or a weekend, I don’t want it to look back further.

The dates are kept in column A, the time in column B, the reading in column C.

It'd be preferred not to use VBA to solve the issue, if that's possible.

Any help would be appreciated.
posted by WCityMike to Computers & Internet (12 answers total) 3 users marked this as a favorite
The below will add together all the readings that occurred in the last 90 days, and divide that number by the number of readings. It assumes that the date you want the average for is A2. Change that value as needed.

posted by nulledge at 8:26 AM on January 19, 2015

I get a division-by-zero error.
posted by WCityMike at 8:34 AM on January 19, 2015

nulledge is close, but I think you need a comparator in the second term. AVERAGEIF will do the same thing as sumif and countif -

=AVERAGEIF($A$1:A2, ">"&A2-90,$C$1:C2)

Obviously this will work best on entries with 90 days of data above them.
posted by muddgirl at 8:36 AM on January 19, 2015

Muddgirl, appreciate it -- I'm not getting an error with yours -- but one thing that concerns me is this -- applying this formula to that column, it seems to be yielding inaccurate values. For example, the very first row of the spreadsheet was a high number, since I wasn't yet on medication (297), yet the average value for that row, using that formula, is 149.85. Given that it only has one value to average, it should be telling me 297 ... haven't tried it with stuff on the more current end of the spreadsheet yet.
posted by WCityMike at 8:39 AM on January 19, 2015

BTW, I missed that "Time" was in column B. My formula assumes column B is your glucose reading, and column C is your converted A1C for each reading. You'll have to change it to match your column labels.

Also, if your data does NOT start on row 1, you'll have to change $A$1 and $C$1 to be whatever number your first row is.
posted by muddgirl at 8:42 AM on January 19, 2015

The problem you're seeing is that the formulas presented so far will look at the entirety of the data and only discard rows that are older than 90 days i.e. they will include future data too.

So your initial row calculation will include the entire data set.
posted by achrise at 8:43 AM on January 19, 2015

Or maybe not, I'm still playing with it. The range specification should be able to take care of the case I was thinking about.
posted by achrise at 8:45 AM on January 19, 2015

Oops. right. Good catch muddgirl.
posted by nulledge at 8:48 AM on January 19, 2015

WCityMike - you're completely correct that, for the first row, the average should be 297. For the second row, the average should be the average of the first two rows, and so on until you build up 90 days of data. I've checked this on my end but it's difficult to troubleshoot without seeing your spreadsheet. I suspect it's because your data does not start in row 1, like I mentioned in my last comment.
posted by muddgirl at 8:49 AM on January 19, 2015

Muddgirl's formula is correct. The "1"s need to match the row in which your data starts, and the "2"s need to match the row number you're trying to compute.
posted by achrise at 8:54 AM on January 19, 2015

I would compare to actual A1Cs and modify by a constant or a percentage that fits the data as well as computing the running average. Depends on how thoroughly you cover the clock with your readings.
posted by michaelh at 8:55 AM on January 19, 2015

Thanks, guys. Muddgirl, you're right -- the first row is my header.

I took the formula and added the A1C conversion formula into it, plus a rounding-up for the first decimal place, and this was my final result:

=ROUNDUP((((AVERAGEIF($A$2:A2, ">"&A2-90,$C$2:C2))+77.3)/35.6),1)
posted by WCityMike at 8:55 AM on January 19, 2015 [1 favorite]

« Older Aftermath of an upper respiratory infection   |   What do you do with those nice little drawstring... Newer »
This thread is closed to new comments.