How to convert average of minutes to average hours in excel?-CodePudding

The average usage in mins i got for 1 person is 351.333333333333 used the query - =QUERY(A3:D1074," select C, AVG (D) group by C"),

Then to get average usage in hours i used the formula =QUOTIENT(N3,60) &":" &IF(LEN(MOD(N3,60))=1,0,"") &MOD(N3,60) the result i got is 5:51.3333333333333

Now i'm unable to made a graph with the values of average usage in hours ..its showing invalid type when i entered the data range of average usage in hours.

please help if there is an other way.

CodePudding user response：

The data you get after converting to hours is text, because you concatenate numbers (hours, minutes) with text (:). This can be easily checked by using the TYPE() formula on the result your conversion: it returns 2, meaning it is text data.

I would suggest simply dividing the data in minutes by 60 and use that in the graph, it should work as it will be numerical data.

CodePudding user response：

try:

=TEXT(E2/24/60/60; "mm:ss")

or directly:

=INDEX(TEXT(QUERY(A3:D, "select avg(D) group by C label avg(D)''")/24/60/60, "[mm]:ss"))