+ Reply to Thread
Results 1 to 7 of 7

Text to average time?

  1. #1
    Registered User
    Join Date
    05-12-2020
    Location
    Sweden
    MS-Off Ver
    16:35
    Posts
    4

    Post Text to average time?

    Hi.
    I have a process managing tool that spits out a bunch of data. Among other tasks, one is to dig deeper into the different processes that for some reason take longer than others to complete. So, I'd like to use excel to find the odd processes in a huge set by coloring those lines in red (or whatever).
    I've cut a small piece of the column containing some of the entries to give an example.

    22 min 50 sec
    44 min 24 sec
    18 min 10 sec
    31 min 51 sec
    #REFERENCE!
    1 min 22 sec
    14 min 43 sec
    12 min 27 sec
    15 min
    14 min 45 sec

    In this case I'd preferably like to be able to write a formula and in it set the maximum deviation from average to 10 minutes and color those cells red. I've tried a bunch of formulas but fail as I come across the cells that doesn't match, like row 5 and 6 and 9.
    Please help.
    Thank You.

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,036

    Re: Text to average time?

    Hi
    please see yellow banner to attach a sheet

  3. #3
    Registered User
    Join Date
    05-12-2020
    Location
    Sweden
    MS-Off Ver
    16:35
    Posts
    4

    Re: Text to average time?

    Unfortunately, I can't attach the whole sheet. It is very big and contains sensitive data. I've added the cut-out to represent the column that I want to process.
    Thanx.

  4. #4
    Registered User
    Join Date
    05-12-2020
    Location
    Sweden
    MS-Off Ver
    16:35
    Posts
    4

    Re: Text to average time?

    This is the same example in a file.
    Attached Files Attached Files

  5. #5
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,115

    Re: Text to average time?

    not entirely sure I follow what you're trying to do but the below would calculate the Average time:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    you would then need to apply similar logic in your Conditional Formula to convert each text string to Time - to compare with the above Average

  6. #6
    Registered User
    Join Date
    05-12-2020
    Location
    Sweden
    MS-Off Ver
    16:35
    Posts
    4

    Re: Text to average time?

    Wow - that works like a charm... although is there a way to make the last row as a parameter (A10 in the example) with COUNTA for example... since I never know how many rows the systems spits out... and it can be a lot...

  7. #7
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,115

    Re: Text to average time?

    yes, you can use something like:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    that will work from A1 to last entry in Col A

+ 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. [SOLVED] Weighted Average of Sequential Time Based Data Series Using Start/End Time Input
    By marcoyul in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-16-2017, 12:10 PM
  2. Replies: 3
    Last Post: 06-12-2014, 09:29 AM
  3. Replies: 2
    Last Post: 06-11-2014, 11:39 AM
  4. Average values if the associated time of occurence falls within a certain time range
    By boarderbrent91 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-02-2014, 01:49 PM
  5. Average Function-caclulate the average time spent
    By darryl in forum Excel General
    Replies: 1
    Last Post: 06-25-2010, 07:36 AM
  6. Average text time
    By tangomj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-12-2007, 08:47 PM
  7. [SOLVED] Average & median of text "time" numbers
    By Max in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-17-2006, 07:20 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