# Thread: AverageIfs to compare AM vs PM hours

1. ## 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. I'm struggling to reproduce your sheet arangement, could you supply a workbook with some sample data/named ranges etc?

3. Here ya go.

4. 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.

5. 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
•