PDA

View Full Version : [SOLVED:] AverageIfs to compare AM vs PM hours



Opv
02-13-2020, 12:00 PM
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?

p45cal
02-15-2020, 09:40 AM
I'm struggling to reproduce your sheet arangement, could you supply a workbook with some sample data/named ranges etc?

Opv
02-15-2020, 11:00 AM
Here ya go.

p45cal
02-15-2020, 12:58 PM
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.

Opv
02-15-2020, 01:08 PM
Indeed. Thanks.