I want to Find the Average of Column J
Columns A and H are formatted in “DD/MM/YY HH:MM”
The function in Column J is
=INT(I2-A2) & " days, " & HOUR(I2-A2) & " hrs, " & MINUTE(I2-A2) & " mins "
Now what i want to do is find the Average of Column J … in the same format
Average = xx days, xx hrs, xx mins
The normal function =AVERAGE(J2:J40) returns an error #DIV/0!
All help is appreciated,
Thank you!
Of course you cannot take averages of arbitrary strings, because they aren’t numbers.
You need something like column K, with a simple formula like =I2-A2, and then take average of that. Then you format the resulting average the same way (say, if you have the average in K18, then use formula like =INT(K18) & " days, " & HOUR(K18) & " hrs, " & MINUTE(K18) & " mins ", or ==INT(K18) & " days, " & TEXT(K18;"HH ""hours,"" MM ""mins""")).
