Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    Hong Kong
    Posts
    76
    Thanks
    11
    Thanked 1 Time in 1 Post

    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.
    Last edited by flippertie; 2013-07-22 at 01:43.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Hong Kong
    Posts
    76
    Thanks
    11
    Thanked 1 Time in 1 Post
    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. #4
    Star Lounger
    Join Date
    Dec 2009
    Location
    Hong Kong
    Posts
    76
    Thanks
    11
    Thanked 1 Time in 1 Post
    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
  •