I have a Datasheet containing Start Time column and End Time column, from those two columns I've calculated the time taken to perform a task using this formula:
=TEXT($E4-$D4,"h:mm:ss")
I want to calculate the average time taken to perform that task. I've tried to use the formula:
=AVERAGE(F4:F31)
But I'll have an error says (#DIV/0)
Are the functions that I'm using correct ?! What function should I use to get the average time ?!
Bellow is a screenshot of the Datasheet
The TEXT function produces a text string that only looks like a number, a date or time, In fact, the resulting text has no numerical value and you need a numerical true time value to AVERAGE.
Just use =$E4-$D4 and format the cell(s) as h:mm:ss. This will leave true time values that you can AVERAGE.
The #DIV/0! error arises because an average is the sum of the numerical values divided by the count of the numeric values. Since you have no numeric values, you are effectively dividing by zero.
