CODEWITHSUNDEEP
excelexcel-formulaexcel-2010average
Chris R
Chris R

Reputation: 39

I need to average cells in a column based on the time of day in another column in excel, i.e. morning, afternoon and night

I am trying to get the averages of a column that meet certain criteria into a single cell. The following formula works: =AVERAGEIFS(tblData[Sys],tblData[Time],">=12:00 PM",tblData[Time],"<6:00 PM") but when I adjust the time values to: =AVERAGEIFS(tblData[Sys],tblData[Time],">=6:00 PM",tblData[Time],"<4:00 AM") I get an error. I'm guessing it's because the time range goes into the next day. Is there a better function to use or a workaround?

Upvotes: 0

Views: 791

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

This formula seems to work for you scenario. It checks for the possibility that the end time is before the start time (but on the next day) and changes the logic accordingly:

=AVERAGE(
      IF(
        IF(tmStart>=tmEnd,
                  (tblData[Time]>=tmStart)+(tblData[Time]<=tmEnd),
                  (tblData[Time]>=tmStart)*(tblData[Time]<=tmEnd))
          =1,tblData[Sys],""))

enter image description here

Upvotes: 1

JB-007
JB-007

Reputation: 2441

It's important to understand this function does, and the underlying intentions are - I elaborate in the what follows, proffer a workable solution, provide a reconciliation, as well as a link to the workbook with screenshot below.

You will need to specify # days spanned - even the scenario that 'worked' for you (i.e.. =AVERAGEIFS(tblData[Sys],tblData[Time],">=12:00 PM",tblData[Time],"<6:00 PM") could, in theory, span 2 (or more) days. The fact that this 'works' (doesn't return #DIV/0!) is that the 'intersection' of conditions is an non-empty set (i.e. {12pm-5pm}).

As I say, if this was intended to be ">=12:00" from day 1 through to "<6pm" the following, there is no way of determining whether this is indeed the case by simply 'comparing the times' (e.g. 12pmvs 6pm).

Screenshot/here refer::

Screenshot - scenario B, Multiple days = 'Y'

=IF($I$5="Y",(SUM(1*($C$5:$C$28*(($D$5:$D$28>=$J$3)+1*($D$5:$D$28<$K$3)))))/SUM((($D$5:$D$28>=$J$3)+1*($D$5:$D$28<$K$3))),IFERROR(AVERAGEIFS(C5:C28,D5:D28,">="&J3,D5:D28,"<"&K3),"times don't intersect! "))

where: I5 = 'Y' or 'N' (i.e. multiple days'). When scenario B is selected, with multiple days = 'Y', outcome = 12.9 which reconciles to a manual calculation.

Upvotes: 0

Related Questions