Quote Originally Posted by abhijit786 View Post
I have attached a workbook .Can you help me to calculate the average of timings of all columns.
The problem is: most of the data are text, and they are in a form that Excel does not recognize as time.


1. For columns AF, AG and AH, it would be ideal to convert the text to numeric Excel time, which you can format as Custom [hhh]:mm:ss if you want a 3-digit hour with leading zeros.

To convert column AF, enter the following formula into AN2, copy into AO2 and AP2, then copy AN2:AP2 into AN3:AP159:

=IF(AF2="","",--(--LEFT(AF2,FIND(":",AF2)-1) & MID(AF2,FIND(":",AF2),6)))

Then copy AN2:AP159 and paste-value into AF2:AH159, format AF2:AH159 as Custom [hhh]:mm:ss, and delete AN2:AP159.

Note: That corrects data entries of the form 000:60:xx, changing them to 001:00:xx.

Then =AVERAGE(AF2:AF159) will work, formatted as Custom [hhh]:mm:ss or [hhh]:mm:ss.000.

Alternatively, array-enter the following formula (press ctrl+shift+Enter instead of just Enter):
=AVERAGE(IF(AF2:AF159="","",--(--LEFT(AF2:AF159,FIND(":",AF2:AF159)-1) & MID(AF2:AF159,FIND(":",AF2:AF159),6)))

2. For column AM, put the following formula into AM2, format as Custom [h]:mm:ss, and copy into AM3:AM159:

=IF(COUNT(H2,I2,E2,F2)<>4,"",H2+I2-E2-F2)

Then =AVERAGE(AM2:AM159) will return the correct value, formatted as Custom [hhh]:mm:ss or [hhh]:mm:ss.000.

Note: The formula in AM2 also corrects miscalculations in AM5, AM47, AM71, AM80 and AM118.