Find Running Average of Daily Totals

**xiangreek** · 03-08-2010, 02:08 PM

Hello All,

I am trying to calculate a running average of daily totals. Basically, I'm carb counting. So I have 2 columns, for simplicity call them A and B. In column A I have the date. In column B I have the carb total for that meal. Column A may have multiple entries for the same day, as sometimes I have 2 or 3 meals plus snacks.

What I want to do is, for all my entries, figure out what the average of all day's totals. I do not care what the daily total is, I just need the average.

For example...

On day 1 I can have 175 carbs (40 + 60 + 60 + 15) and on day 2 I might have 160 ( 40 + 60 + 60). This would mean that, on average for these 2 days, I have 167.5 carbs a day. If I had 200 carbs the third day, this would change the average to 178.3 carbs a day.

The problem is that if I just find the average of all entries, regardless of date, the figure is off. My meals have around 60 carbs, but my snacks only have about 15.

So if I were to just add all entries for the first two days divided by the number of entries it would not give me the correct data: 40 + 60 + 60 + 15 + 40 + 60 + 60 / 7 = 47.9 carbs (instead of 167.5).

**DonkeyOte** · 03-08-2010, 02:13 PM

You might get away with:

=SUM($B$2:$B$1000)/MAX(1,SUMPRODUCT(($A$2:$A$100<>"")/COUNTIF($A$2:$A$100,$A$2:$A$100&"")))

where B2:B1000 contains your carb values and A2:A1000 your day/date values.

**xiangreek** · 03-08-2010, 02:26 PM

Hi! Thanks for the quick response.

I modified your formula. My dates begin in A3 and so far go as far as A27. My carb totals start at G3 and go as far as G27. Each row always has a date, but there isn't always a carb amount (some times I add a note in a third column with nothing in the carb column).

=SUM($g$3:$g$27)/MAX(1,SUMPRODUCT(($A$3:$A$27<>"")/COUNTIF($A$3:$A$27,$A$3:$A$27&"")))

Whether I hit Enter or CTRL-SHIFT Enter it would always come back with 0.