Ask Your Question
0

Finding the Average

asked 2018-09-19 10:36:45 +0200

Anusorn gravatar image

C:\fakepath\Untitled.png

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!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2018-09-19 10:51:40 +0200

updated 2018-09-19 10:52:08 +0200

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""")).

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-09-19 10:36:45 +0200

Seen: 65 times

Last updated: Sep 19 '18