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
---

=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

=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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
>
> =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.

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" <[email protected]> wrote in message
news:[email protected]...
>
> =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, 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" <[email protected]> wrote in message
news:[email protected]...
>
> =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
>

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

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

Yes, it did <g>. Thanks again, Dav !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dav" <[email protected]> wrote in message
news:[email protected]...
>
> 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