Avg Formula on a Duration Column

Denise Pfeil

I have read the forum and still having issues with this formula. I am simply trying to average the duration column. It returns a Divided by Zero error and have tried adding this into my formula. What is a sheet summary formula that would average the duration column. Any help is greatly appreciated :)

Brandon Austin

Hi Denise .

Hope you are well!

By Duration do you only need Hours:Minutes ?

You will have to have a Sum Formula 1st and then Use a INT Formula to calculate it back

Here is an example of my sheet and formulas that I used.

SUM:

=(VALUE(LEFT([TIME OUT:]@row; FIND(":"; [TIME OUT:]@row) - 1)) + (VALUE(RIGHT([TIME OUT:]@row; 2)) / 60) + ([DATE OUT:]@row - [DATE IN:]@row) * 24) - (VALUE(LEFT([TIME IN:]@row; FIND(":"; [TIME IN:]@row) - 1)) + (VALUE(RIGHT([TIME IN:]@row; 2)) / 60))

DURATION:

=INT([SUM:]@row) + ":" + IF(([SUM:]@row - INT([SUM:]@row)) * 60 < 10; "0") + ([SUM:]@row - INT([SUM:]@row)) * 60

Kind Regards

Brandon