# Average & median of text "time" numbers

1. ## 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
---

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

3. ## Re: 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
>
>

4. ## Re: 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" <Dav.2coexd_1155804611.3902@excelforum-nospam.com> wrote in message
news:Dav.2coexd_1155804611.3902@excelforum-nospam.com...
>
> =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.

5. =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. You know a full day is define as 1 in a date/time value, so this is what it needs to be to work out an average. eg average of 2300 and 0200 is 0030, not 1230, to perform this I have added 1 (24hrs to each value < midday)

Regards

Dav

6. ## Re: 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" <Toppers@discussions.microsoft.com> wrote in message
news:92D55592-9069-4224-B7E6-1F330504021C@microsoft.com...
>
> =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)

7. ## Re: 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" <Dav.2coi62_1155808807.4651@excelforum-nospam.com> wrote in message
news:Dav.2coi62_1155808807.4651@excelforum-nospam.com...
>
> =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
>

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

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

10. ## Re: Average & median of text "time" numbers

Yes, it did <g>. Thanks again, Dav !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dav" <Dav.2cokhf_1155811807.2268@excelforum-nospam.com> wrote in message
news:Dav.2cokhf_1155811807.2268@excelforum-nospam.com...
>
> 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

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1