+ Reply to Thread
Results 1 to 10 of 10

Average & median of text "time" numbers

  1. #1
    Max
    Guest

    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. #2
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    =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. #3
    Toppers
    Guest

    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. #4
    Max
    Guest

    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. #5
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    =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
    Last edited by Dav; 08-17-2006 at 06:03 AM.

  6. #6
    Max
    Guest

    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. #7
    Max
    Guest

    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. #8
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    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. #9
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    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. #10
    Max
    Guest

    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




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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