Consulting

Results 1 to 5 of 5

Thread: AverageIfs to compare AM vs PM hours

  1. #1
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location

    AverageIfs to compare AM vs PM hours

    The following formula, AVERAGEIFS(INDIRECT(B$2),DATESONLY,">="&TODAY()-$G848), provides the average based on the number of days specified in cell, $G848, where:
    INDIRECT(B$2) represents a named range of data corresponding to size of the DATESONLY range.
    DATESONLY represents the date and time for each entry in the format, "02/13/2020 9:30 AM".
    $G848 represents the designated number of days for which the average is sought.

    I am attempting to expand the formula to allow me to distinguish the average for AM vs PM hours. Is this possible without a help column which extracts the hour for each cell within the DATESONLY range?

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    I'm struggling to reproduce your sheet arangement, could you supply a workbook with some sample data/named ranges etc?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Here ya go.
    Attached Files Attached Files

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    In C55, array-entered (means committing the formula to the sheet with Ctrl+Shift+Enter, not just Enter)
    =AVERAGE(IF(((DATESONLY>=TODAY()-G55)*(HOUR(DATESONLY)<=12))>0,INDIRECT(B$2)))
    and in D55:
    =AVERAGE(IF(((DATESONLY>=TODAY()-G55)*(HOUR(DATESONLY)>12))>0,INDIRECT(B$2)))
    , copied down
    Note that the only difference is <=12 and >12. You may want to tweak this depending on how you define AM and PM hours to <12 and >=12, but note also that only one of these formula should contain an = sign, otherwise you could be including some numbers in both AM and PM averages, and if you don't include an = sign in either you'll be missing out noon values from both averages.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Indeed. Thanks.

Posting Permissions

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