average time based on clock time range

[xrayexceller]

I would like to average clock times that fall within a specific time frame.

I have already calculated an elapsed time with a formula but want to find the average time between 8:00 and 20:00 and between 20:01 and 7:59.
Any ideas?

Thanks in advance

Performed time Ordered to radiology report (min)
7:20 37
8:59 61
14:24 99
17:34 105
17:47 939
19:19 77
22:46 52
22:48 66
23:06 47

[FDibbins]

this will work for the 1st part...
=AVERAGEIFS(B1:B9,A1:A9,">="&8/24,A1:A9,"<="&20/24)

Tring to figure out why the opposite wont work...
=AVERAGEIFS(B1:B9,A1:A9,"<"&8/24,A1:A9,">"&20/24)
gives #DIV/0

[Tony Valko]

Tring to figure out why the opposite wont work...
=AVERAGEIFS(B1:B9,A1:A9,"<"&8/24,A1:A9,">"&20/24)
gives #DIV/0

The time can not be both <8:00 AM and >8:00 PM at the same time because the clock rolls over to 0 at midnight.

[FDibbins]

@ Tony...OK I can see that, but if I change that to...
=AND(A1<8/24,A1>20/24)
copied down, I get TRUE's where I should, and FALSE's where I should? (and swapping that 1, swaps the T/F's around too)

So what would you suggest for this?

[daddylonglegs]

...=AND(A1<8/24,A1>20/24)......I get TRUE's where I should, and FALSE's where I should.....

Are you sure? That formula will always return FALSE no matter what the value of A1, you'd need OR in place of AND to get correct results...

So what would you suggest for this?

If you use the above formula with OR then you can use AVERAGEIF based on the heplper column...... or without a helper you can use an array formula like

=AVERAGE(IF((A1:A9<8/24)+(A1:A9>20/24),B1:B9))

confirmed with CTRL+SHIFT+ENTER

[Tony Valko]

Data in the range A1:B9

D1 = 20:01
E1 = 7:59

Then, this array formula**:

=AVERAGE(IF((A1:A9>=D1)+(A1:A9<=E1),B1:B9))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[FDibbins]

Sorry guys, my bad, I actually did mean to type OR

Thanks for the assist

[Tony Valko]

..........

[xrayexceller]

Thanks for this. I modified the above formula to work with my spreadsheet and it worked as set
{=AVERAGE(IF((E5:E454<8/24)+(E5:E454>20/24),Q5:Q454))}
but I would like to find the average for a filtered list, stat vs routine study type. Can this be done?

[daffodil11]

Does the word Stat or Routine Study exist in the rows?

You could add an additional IF condition

{=AVERAGE(IF(range of rows with Stat and Routine="Stat",IF((E5:E454<8/24)+(E5:E454>20/24),Q5:Q454)))}

or

{=AVERAGE(IF(range of rows with Stat and Routine="Routine Study",IF((E5:E454<8/24)+(E5:E454>20/24),Q5:Q454)))}

[xrayexceller]

Yes but in column B

[daffodil11]

{=AVERAGE(IF($B$1:$B$454="Stat",IF((E5:E454<8/24)+(E5:E454>20/24),Q5:Q454)))}

or

{=AVERAGE(IF($B$1:$B$454="Routine Study",IF((E5:E454<8/24)+(E5:E454>20/24),Q5:Q454)))}

[Tony Valko]

Thanks for this. I modified the above formula to work with my spreadsheet and it worked as set
{=AVERAGE(IF((E5:E454<8/24)+(E5:E454>20/24),Q5:Q454))}
but I would like to find the average for a filtered list, stat vs routine study type. Can this be done?

If I understand what you want to do, try this.

Assuming B5:B454 contains "stat" or "routine study".

This array formula**:

=AVERAGE(IF(SUBTOTAL(3,OFFSET(B5,ROW(B5:B454)-ROW(B5),0,1)),IF((E5:E454<8/24)+(E5:E454>20/24),Q5:Q454)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

If that isn't what you had in mind can you post a SMALL sample file that shows us what result you expect?

SMALL = about 20 rows worth of data

[xrayexceller]

Thanks, that did the trick.

[Tony Valko]

Can't tell who you're replying to so I'll just say this...

You're welcome. We appreciate the feedback!