Find average positive numbers and negative numbers of duration time(X)?

[PhilipeC]

Duration time is:


F4:F20 IS ENTRY TIME FROM I4:I20
D4:D20 IS EXIT TIME FROM I4:I20
DURATION TIME(X) IS F4:F20 - D4:D20


Such as if D4= 11:00 AND F4= 12:00 than (EXIT TIME)F4- (ENTRY TIME)D4 = 12:00 - 11:00 = 1:00 , Duration time is 1:00


I want to find the AVERAGE duration time of positive numbers I4:I20 and another formula to find the average duration time of negative numbers I4:I20?

 

[AhoyNC]

Try:
These are array formulas that must be entered with CTRL-SHIFT-ENTER.

Positive

AVERAGE((IF(F4:F20-D4:D20>0,F4:F20-D4:D20)))

Negative

AVERAGE((IF(F4:F20-D4:D20<0,F4:F20-D4:D20)))

 

[PhilipeC]

Hi , What about I4:I20?

I4:I20 all the positive numbers, i want the average duration time(x) which is calculated F4:F20 - D4:D20? So first the formula must determine the positive numbers from I4:I20 than calculate the average duration time(x)F4:F20 - D4:D20 from these columns?