Subtraction of Column Ranges within Averageifs Function

Hi Guys,

Always getting back to this excellent forum when I get stuck

This time I have a situation like this:
Column A - Dates
Column B - Equipment Type
Column C - Load Start Time
Column D - Load End Time

I need to do some statistical analysis (average, mode, medium, st dev) on the load times, which is load start time - load end time. As far as I know, I can't subtract ranges (column d i column c) within Averageifs function. I assume I would need to use an array function for this.

Would anyone please be able to assist with this?

Many thanks!

Would the last one be an array formula as well?

Thank you so much Robert, priceless help!

Is this formula OK:

{=AVERAGE(IF(OR($E$2:$E$603="C",$E$2:$E$603="D",$E$2:$E$603="G",$E$2:$E$603="H"),$L$2:$L$603-$K$2:$K$603))}

I'm asking because it's giving me the same result with A,B,E,F criteria and C,D,G,H.

Your formula works! But I have just checked all the cells, non of them was empty or negative (in one instance though the difference L-K was negative). But that wouldnt realy matter right?

Would I be able to use something like this (to make sure start date is not greater than end date, as I had such instance):

=AVERAGE(IF(TEXT($L$2:$L$603-$K$2:$K$603,"[hh]:mm")>1,IF($E$2:$E$603="A",$L$2:$L$603-$K$2:$K$603)))

Thank you for that. I have tried this one:

=AVERAGE(IF(TEXT($L$2:$L$603-$K$2:$K$603,"[hh]:mm")>"01:00",IF($E$2:$E$603="A",$L$2:$L$603-$K$2:$K$603)))

Its giving me an error "value not available to the formula". Any clue?

My dates are formated to show something like 27/2/2014 6:00. I want to exclude all values where the difference between the dates is less that say 10hrs, because in reality ships cant be loaded in that much time. Something like this works:

=AVERAGE(IF($L$2:$L$603-$K$2:$K$603>0.25,IF($E$2:$E$603=U3,$L$2:$L$603-$K$2:$K$603)))

I assume 0.25 in the instance above would be equal to 0.25x24hrs=6hrs

Thank I've solved it in the meantime.