Working hours average

[Aviral Sharma]

Hi
I have a database of a person's working hours where I have his three months
extra or less working hours. I want an average of all the three months.

Can someone help me with the formula for this average. Here the average
formula is not giving the correct value.

Month 1 Month 2 Month 3 Average
-08:31:00 -24:50:00 21:01:00 ?

 

[DILipandey]

Hi Aviral,

Assuming the data is there in columns A, B,C and D respectively, use
following formula in column D:-

=AVERAGE((VALUE(IF(LEFT(A1,1)="-",MID(A1,2,2)&":"&MID(A1,5,2),MID(A1,1,2)&":"&MID(A1,4,2)))),(VALUE(IF(LEFT(B1,1)="-",MID(B1,2,2)&":"&MID(B1,5,2),MID(B1,1,2)&":"&MID(B1,4,2)))),(VALUE(IF(LEFT(C1,1)="-",MID(C1,2,2)&":"&MID(C1,5,2),MID(C1,1,2)&":"&MID(C1,4,2)))))

Please click on "yes" if it works

Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India