Thread: Groupings of data and averaging

1. Groupings of data and averaging

I have to track my glucose levels, and report weekly, biweekly, and monthly averages to my doctor when I see her.

It seemed to me that an Excel spread sheet would be perfect for this, but suddenly I find that it is more cumbersome than I thought it would be. So I turn to the collective wisdom here. I'm a medievalist (English), not a programmer, so bear with me.

Here is what I have so far: a small spread sheet with 5 columns (date, reading, 7 day average, 14 day average, month average). The formula for calculating the 7 day average is:

IF (ISBLANK(B2),"",AVERAGE B\$2:B\$8)

I copy this down for 7 days, but then need to change the range. So far I've done it by hand, but there must be a better way.

Starting with day 8, the formula needs to change so the average is calculated for B\$9:B\$16). For the next seven day period, the range would be B\$17:B\$23, and so on. The formulas for the 14 day period are similar, but the range is longer. And I haven't thought about the montly averages yet.

So my question is: How do I to change the formula for averages so it does it for the 7 day period, and when the 8th day rolls around, and I copy the formula down to the 8th day, the range to be averaged changes to the next 7 days (or 14; or next month).

Any pointers would be appreciated. I am using Excel 2010, in case that is helpful.

schipper [Bill]

2. Hi Bill

Do you need the averages against every date? If you just calculate the average every week, a formula with relative references can just be copied down the whole sheet as it won't appear on inappropriate dates.

The formulas below need to be started on the first row on which they're needed else the top of the range is off the sheet. I created a spreadsheet with 31/5/2013 at the top (because it is 31 days before the end of this month). The formula for weekly starts on the 9th June, biweekly on 16th June and monthly on 30th June and are just copied "as is" down the sheet. For simplicity I've copied the formulas from row 32, the end of June, where all the formulas show a result.

To get the monthly average, if it is calendar month, add a column after the date that contains the month:

(col B):
=MONTH(A2)

weekly (col C):
=IF(AND(C26<>0,WEEKDAY(A32,1)=1),AVERAGE(C26:C32), "")

biweekly (col D):
=IF(AND(D32<>"",MOD(WEEKNUM(A32,1),2)=1),AVERAGE(C 19:C32),"")

monthly (col E):
=IF(AND(B33<>B32,SUMIF(B2:B32,B32,C2:C32)>0),AVERA GEIF(B2:B32,B32,C2:C32),"")

Basically the formulas just return a value at the end of the desired period. The period end is determined for weekly by the day of the week, to change the day change the "=1" to some other number. 1 = Sunday, 2=Monday etc. For biweekly I added a check for an odd week number which only happens every other week (except new year...) Change the "=1" to "=0" for even weeks. For monthly I look for an upcoming change of month, and the average is restricted to dates in the current month by using "averageif" instead of plain "average". Note that the formula requires a reading in the past calendar month.

One could get a lot more clever, but that might do the job for you.

Ian.

PS If you get fed up with all the green flags on the formulas cells, click the file menu, choose options, pick Formulas and untick the box near the bottom for "formulas which omit cells in a region".

PPS Another approach, which could give averages on every row, is to use the "row", "mod" and "offset" functions to force the averaging range to jump down the sheet 7 days at a time, but the formulas for that would look very obscure!

3. You are on the right track. You just need to use relative (no \$) ranges instead of absolute (\$) ranges. That way you'll have a continuous moving average. Also, for monthly, why not just use 30 day average?

So for 7 day moving average, beginning in cell C8, enter =IF(ISBLANK(B8),"",AVERAGE(B2:B8)). For 14 day moving average, beginning in cell D15, enter =IF(ISBLANK(B15),"",AVERAGE(B2:B15)). For 30 day moving average, beginning in cell E31, enter =IF(ISBLANK(B8),"",AVERAGE(B2:B31)). In column A, you could enter a start date in cell A2, in cell A3, enter =A2+1, and then copy A3 down for as many dates as you'd want. Then copy the 7, 14, and 30 day formulas down through the range of dates you just created. From then on, just enter your daily glucose level in column B each day.

4. Thanks Ian.

Worked like a charm.

One question (regarding your PS). I'm using three columns at the moment (A=date; B=reading; C=). The formula you gave for weekly averages indeed shows the little green flags because I'm averaging numbers in column B. But why does Excel insist that I should include column A in the averaging? I've turned it off, but the question remains. I'm a teacher, and frequently average results in a single column for class records, but never in adjacent ones. That would make no sense to me.

Bill

5. I believe that the green warning flag is not about including column A, but the fact that you have only included a subset of column B. Excel is warning you that there are more numbers in column B that it believes should be included in the average. The warning is a false positive since you purposely are including only a subset

Steve

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•