Average day and time problem 1

[ddrafts]

My column is C the data looks like this

0:02:52:02
3:22:44:40
5:07:01:50
0:00:36:43
18:25:26:10

The first one is the number of days, second is the number of hours, third is the number of minutes, and the last is the number of seconds. I need to get the average of them.

I have tried =AVERAGE(C14:C18) and get #DIV/0! So I then tried to add them up with =SUM(C14:C18) and get 0. I tried turning the format to Number and get 0.00. Then I tried to turn them to Time (dd:hh:mm:ss) and still get all Zeros. Is their any way to do this?

Thanks in advance
Doug

 

[ddrafts]

Sorry I forgot that this is Excel 2003.

 

[SkipVought]

Doug,

faq68-5827.

So, the simplest way is to parse your string using the COLON as the delimiter Data > text to columns

Then ist a simple math problem of converting everything to days.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

[tt]
2:52:02
94:44:40
127:01:50
0:36:43
457:26:10
136:32:17 AVERAGE
[/tt]
took about 45 seconds.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

Where are you getting the 'numbers' from?

Right now the problem is that the contents of each cell is actually a string; a string, what is more, that cannot be coerced into a legitimate number apart from 0 (as you have found)

 

[ddrafts]

I was getting the numbers from a SQL database. What I ended up doing is taking the : out. That gave me the information that I needed. I want to thank everyone for the help.

Doug

 

[SkipVought]

"What I ended up doing is taking the : out."

Please explain to all of us how you accomplished this and what AVERAGE value you arrived at for the example you posted.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GSCaupling]

Doug,

If you simply removed the colons and computed an average of 5,315,209, this number is useless for your purposes.

It's just the average of

[tt] 25,202
3,224,440
5,070,150
3,643
18,252,610[/tt]

and has nothing to do with hours/minutes, etc.

BTW: 18:25:26:10 is not valid (the "25 hours" part).

GS


[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]

 

[ddrafts]

GScaupling

Thank you for making me understand my mistake. What I did was take out the part in my SQL query that converted all the seconds to the days, hours, min, and sec. I left it as seconds. I then Averaged them out and then used =A1/(60*60*24) and formatted the cell dd:hh:mm:ss and that gave me the answers that I was looking for.

Thanks again.

 

[SkipVought]

"...formatted the cell dd:hh:mm:ss and that gave me the answers that I was looking for."

Well that will ONLY be true IF, and ONLY IF, your Average comes out less than 32.

Do a format on 32.00001 and see that you do NOT get
[tt]
32:.........
[/tt]
Rather you get
[tt]
01:.........
[/tt]
You obviously did not take the time to read and understand the FAQ that I posted for your benefit.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

If you want to show your data in a dd:hh:mm:ss format, you must generate a STRING like this...
[tt]
=INT(A1)&":"&TEXT(A1-INT(A1),"hh:mm:ss")
[/tt]


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I realized I made this more complex than needed...
[tt]
=INT(A1)&":"&TEXT(A1,"hh:mm:ss")
[/tt]
The essence of your error is that the dd format returns the DAY OF MONTH in the referenced number, and not the NUMBER OF DAYS in the referenced number. Hence only values less than 32 work.


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[626F62]

ok reading all that got confusing, and i dont know off the top of my head but if you search for 'Mode' in the excel help it gives explinations of working out the average...

Mean
Median
Mode

think its mode that you would need but im sure you could work it out.

hope that helps if you havnt already sorted it out..

 

[strongm]

That's not the issue here.