Calculating Average of Time

Sir,

I have excel sheet in which I want to calculate the average of the time .kindly help me to calculate the average of each column.

Regards

Sir,

I have attached a workbook .Can you help me to calculate the average of timings of all columns.

Regards

sheet is not protected I tried it in different way and then asked for help

Originally Posted by abhijit786

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):

Please Login or Register  to view this content.

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.