Average & median of text "time" numbers

M

Max

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
 
D

Dav

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

Toppers

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

Dav

=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
finish, if it is before midday then it is the next day, if this is th
case i have added 1 to the value. I think this is what you want as a
average

Regards

Da
 
M

Max

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

Max

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

Dav

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

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

how is your data entered in the spreadsheet. If it is numbers formate
with leading 0's the formula will not work, but you said it was text i
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

Da
 
D

Dav

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

Regards

Da
 
Top