+ Reply to Thread
Results 1 to 3 of 3

Average text time

  1. #1
    Registered User
    Join Date
    07-12-2006
    Posts
    54

    Talking Average text time

    A B C D
    1 1/2/07 12:01 PM 1/10/07 5:51 PM 8:5:50 197
    2 1/5/07 12:01 PM 1/10/07 8:51 AM 4:20:50 116
    3 1/2/07 12:01 PM 1/10/07 11:51 PM 8:11:50 203

    5 #DIV/0 #DIV/0
    6 #DIV/0 #DIV/0

    C1=TEXT(B1-A1, "d:h:mm")
    C2=TEXT(B2-A2, "d:d:mm")
    C3=TEXT(B3-A3, "d:h:mm")

    C5=AVERAGE(C1:C3)
    C6=AVERAGE(D1:D3)

    How can I get an average in "d:h:mm" and in "h" when using a text cell calculation, without getting the #DIV/0 error?

    Do I need to convert the text cell calculation result into a numerical value?

    Thx

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325
    Naming your range "list" the formula would be :
    =AVERAGE(IF(NOT(ISERROR(list)),list,FALSE))
    Enter as array with Ctrl+Shift+Enter

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Simplest way is to not use TEXT function, e.g. in C1 just use

    =B1-A1 and use cell formatting to display as d:h:mm

    You can then get an average in the usual way

    Note d:h:mm format (even within TEXT function) won't show days greater than 31

+ 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