+ Reply to Thread
Results 1 to 10 of 10

Formula for Filtered Time Average

  1. #1
    Registered User
    Join Date
    03-26-2018
    Location
    Shanghai, China
    MS-Off Ver
    Office 2016
    Posts
    7

    Angry Formula for Filtered Time Average

    I used =TEXT(G2-G3,"[h]:m") in Office 2016 to calculate the delivery time between stages for multiple orders. Now I have filtered the various stages of the order delivery to only show deliveries between two cities. I want to calculate the average delivery time (hours, minutes) per city, but all of the formulas i have tried--subtotal, average, averageif, averagea, averageifs, vlookup--have all failed. How do I calculate the average time (hours: minutes) for a column of filtered times (calculated by =TEXT(G2-G3,"[h]:m")? Been working on this for several days and would desperately appreciate some help, thank you.

    屏幕快照 2018-03-26 下午1.58.39.png

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Formula for Filtered Time Average

    Don't use TEXT ... that's what you get. Just use G2-G3 and use a Custom format "[h]:m". Then SUBTOTAL should work.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Formula for Filtered Time Average

    Or you can use something like this:

    =ABS(TIME(HOUR(B3),MINUTE(B3),SECOND(B3))-TIME(HOUR(B4),MINUTE(B4),SECOND(B4)))

    and use SUBTOTAL as TMS said
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-26-2018
    Location
    Shanghai, China
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Formula for Filtered Time Average

    TMS, thanks for your advice. I tried =(B3-B2,"[h]:m"), but it won't work, when I click "Enter" a sign pops up saying something is wrong and it can't be completed
    屏幕快照 2018-03-26 下午3.09.01.png

  5. #5
    Registered User
    Join Date
    03-26-2018
    Location
    Shanghai, China
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Formula for Filtered Time Average

    PaulM100, thanks for the formula and file, I tried it and the result was there, just in decimals. Is there a way I can change the formula to show it in h:m?
    屏幕快照 2018-03-26 下午3.07.56.png

  6. #6
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Formula for Filtered Time Average

    Right click - Format - Custom - Type in the box [h]:mm - Ok

    Should be changed afterwards. Also, the ABS in formula changes the numbers to positive. If you want negative numbers remove it.

  7. #7
    Registered User
    Join Date
    03-26-2018
    Location
    Shanghai, China
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Formula for Filtered Time Average

    If it doesn't change is there a code to fix that?

  8. #8
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Formula for Filtered Time Average

    It will work. If not let us know.

  9. #9
    Registered User
    Join Date
    03-26-2018
    Location
    Shanghai, China
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Formula for Filtered Time Average

    Hey I finally figured it out! ABS didn't work for me, I ended up changing the format of both columns to "time hh:mm:ss" and then going with the class =G3-G2 and subtotal 1 for average. thank you so much for your advice, have a great day!

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Formula for Filtered Time Average

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 06-17-2015, 03:35 PM
  2. Replies: 3
    Last Post: 06-12-2014, 09:29 AM
  3. Replies: 2
    Last Post: 06-11-2014, 11:39 AM
  4. [SOLVED] Include only filtered data in an average formula
    By Consty1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-18-2013, 09:59 AM
  5. average formula for time values only
    By davechamp1983 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-09-2013, 09:22 AM
  6. [SOLVED] Filtered Average
    By sick stigma in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-24-2012, 11:40 PM
  7. Replies: 6
    Last Post: 11-12-2011, 12:38 AM

Tags for this Thread

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