# Thread: Moving average, variable date range and number of entries per day?

1. ## Calculate average, variable date range and number of entries per day?

I have a data table with a date/time stamp in column A and corresponding values in columns B, C, D. The readings are sporadic - there may be any number from 0 to 3 entries on a given day. The table is sorted on the time-stamp, and has no blank lines or empty values.

I have averages for each complete column, and the row values update automatically as data is added to the table. e.g.

=AVERAGE(DataTable!\$B\$2:\$B\$106)

I have two ranges defined: StartDate & EndDate, and I now want to calculate average values for the given date range.
ie: "Show me the average of values in Col B where corresponding date/time in Col A is >= StartDate and <= EndDate"

I've been trying combinations of AVERAGE, INDEX, & MATCH functions with StartDate & EndDate without success.

Any pointers or examples would be appreciated.

2. If you are using XL2007 or greater the easiest solution is AVERAGEIFS function something like:
=AVERAGEIFS (DataTable!\$B\$2:\$B\$106, DataTable!\$A\$2:\$A\$106, ">="&StartDate, DataTable!\$A\$2:\$A\$106, "<="&EndDate)

If your version pre-dates AVERAGEIFS function, you can use an array formula (confirm with ctrl-shift-enter) of the form:
=AVERAGE(IF ((DataTable!\$A\$2:\$A\$106 >= StartDate)*(DataTable!\$A\$2:\$A\$106 <=EndDate), DataTable!\$B\$2:\$B\$106))

Steve

3. ## The Following User Says Thank You to sdckapr For This Useful Post:

flippertie (2013-07-22)

4. Thanks - works perfectly!

I'm on XL2007, but most of my knowledge comes from XL2003 and earlier (back to lotus 123!) I wasn't aware of the AverageIf function. I don't need to use Excel very frequently so new innovations pass me by..

5. Quick addendum for anyone else using this formula - I had to change it slightly

from this
=AVERAGEIFS (DataTable!\$B\$2:\$B\$106, DataTable!\$A\$2:\$A\$106, ">="&StartDate, DataTable!\$A\$2:\$A\$106, "<="&EndDate)

to this
=AVERAGEIFS (DataTable!\$B\$2:\$B\$106, DataTable!\$A\$2:\$A\$106, ">="&StartDate, DataTable!\$A\$2:\$A\$106, "<="&EndDate+1)

Note the added '+1' at the end.

This is needed because a date entered manually in EndDate evaluates to an integer (22 July 13 = 41477) but readings taken during the day will be fractionally greater than that (eg 6:00 am on 22 july 13 is 41477.25) and readings from EndDatewere being omitted from the calculation..

#### Posting Permissions

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