Average & median of text "time" numbers
 

In A2:A9 are text "time" numbers which may cross midnight

1941
1852
0130
2347
0242
2326
0028
2257

Looking for formulas to place in A10:A11
which can return the average & median of the times in the same text "time"
format
Thanks
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Average & median of text "time" numbers
 

=TEXT(AVERAGE(TIME(LEFT(A2:A9,LEN(A1)-2),RIGHT(A2:A9,2),0)),"hhmm")

=TEXT(median(TIME(LEFT(A2:A9,LEN(A1)-2),RIGHT(A2:A9,2),0)),"hhmm")

both formulas are arrays so need to be enter with ctrl shift enter to
work

the crossing midnight bit is a bit unclear, if it affects the above,
you will have to explain more.

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=572543

Average & median of text "time" numbers
 

=TEXT(AVERAGE(TIME(LEFT($A$2:$A$9,2),RIGHT($A$2:$A $9,2),0)),"hhmm")

=TEXT(MEDIAN(TIME(LEFT($A$2:$A$9,2),RIGHT($A$2:$A$ 9,2),0)),"hhmm")

entered as array formulas (Ctrl+Shift+Enter)

"Dav" wrote:


=TEXT(AVERAGE(TIME(LEFT(A2:A9,LEN(A1)-2),RIGHT(A2:A9,2),0)),"hhmm")

=TEXT(median(TIME(LEFT(A2:A9,LEN(A1)-2),RIGHT(A2:A9,2),0)),"hhmm")

both formulas are arrays so need to be enter with ctrl shift enter to
work

the crossing midnight bit is a bit unclear, if it affects the above,
you will have to explain more.

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=572543

Average & median of text "time" numbers
 

Thanks, but I got #VALUE! with both

Here's a sample with your suggestions in:
http://cjoint.com/?irlpmeAXkb
Av n median of text time numbers.xls

the crossing midnight bit is a bit unclear, if it affects the above,
The source times are in text, and are log-off times for consecutive days
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dav" wrote in message
...

=TEXT(AVERAGE(TIME(LEFT(A2:A9,LEN(A1)-2),RIGHT(A2:A9,2),0)),"hhmm")

=TEXT(median(TIME(LEFT(A2:A9,LEN(A1)-2),RIGHT(A2:A9,2),0)),"hhmm")

both formulas are arrays so need to be enter with ctrl shift enter to
work

the crossing midnight bit is a bit unclear, if it affects the above,
you will have to explain more.

Average & median of text "time" numbers
 

=TEXT(AVERAGE(IF(TIME(LEFT(A2:A9,2),RIGHT(A2:A9,2) ,0)0.5,TIME(LEFT(A2:A9,2),RIGHT(A2:A9,2),0),TIME( LEFT(A2:A9,2),RIGHT(A2:A9,2),0)+1)),"hhmm")

should work entered as an array shift ctrl enter

I tested it based on your spreadsheet link and it worked

I have said that if the finish time is after midday (0.5) it is a
finish, if it is before midday then it is the next day, if this is the
case i have added 1 to the value. I think this is what you want as an
average

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=572543

Average & median of text "time" numbers
 

Thanks, that returns some results but I'm not sure whether the results are
meaningful.

I'll explain .. The source set of log-off times in A2:A9 are "scrambled",
viz:

1941
1852
0130
2347
0242
2326
0028
2257

If I were to "sort" the times going by the earliest log-off time to the last
log-off, it'll appear as:

1852
1941
2257
2326
2347
0028
0130
0242

So I believe the "average" log-off should be a figure between the earliest
1852 and the last 0242. Your formula returns: 1410 for the average which is
out of range.

For the median, think it should be the midpoint between the times 2326 and
2347. Your formula returns: 1916 for the median.

Further insights appreciated ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Toppers" wrote in message
...

=TEXT(AVERAGE(TIME(LEFT($A$2:$A$9,2),RIGHT($A$2:$A $9,2),0)),"hhmm")

=TEXT(MEDIAN(TIME(LEFT($A$2:$A$9,2),RIGHT($A$2:$A$ 9,2),0)),"hhmm")

entered as array formulas (Ctrl+Shift+Enter)

Average & median of text "time" numbers
 

Dav, Looks good, thanks! I replaced average with median (for the median
formula), and the result: 2336 gells with the clarification I posted to
Toppers in the other branch.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dav" wrote in message
...

=TEXT(AVERAGE(IF(TIME(LEFT(A2:A9,2),RIGHT(A2:A9,2) ,0)0.5,TIME(LEFT(A2:A9,2),RIGHT(A2:A9,2),0),TIME( LEFT(A2:A9,2),RIGHT(A2:A9,2),0)+1)),"hhmm")

should work entered as an array shift ctrl enter

I tested it based on your spreadsheet link and it worked

I have said that if the finish time is after midday (0.5) it is a
finish, if it is before midday then it is the next day, if this is the
case i have added 1 to the value. I think this is what you want as an
average

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile:
http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=572543

Average & median of text "time" numbers
 

2336 is the median which would correspond to the midpoint between 2326
and
2347

2310 is the average
this assumes the data is entered as text so 0028 is what is entered in
the cell not 28 formated as 0000

how is your data entered in the spreadsheet. If it is numbers formated
with leading 0's the formula will not work, but you said it was text in
your initial post.

if it is numbers

=TEXT(MEDIAN(IF(TIME((B2:B9)/100,MOD(B2:B9,100),0)0.5,TIME((B2:B9)/100,MOD(B2:B9,100),0),TIME((B2:B9)/100,MOD(B2:B9,100),0)+1)),"hhmm")

entered as an array shft ctrl enter

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=572543

Average & median of text "time" numbers
 

Being in linear mode it was not clear who you were answering, so
disregard my last post. Glad it is working, out posts must have
crossed!

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=572543

Average & median of text "time" numbers
 

Yes, it did <g. Thanks again, Dav !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dav" wrote in message
...

Being in linear mode it was not clear who you were answering, so
disregard my last post. Glad it is working, out posts must have
crossed!

Regards

Dav